Post reply

default date format

  • November 17, 2003 at 7:25 am

    #59551

    this has got to be annoyingly simple but how do you change the default date format?

    select getdate()

    returns 2003-11-17 14:05:33.013.

    How do I get 17-11-2003?

    Thanks

  • November 17, 2003 at 7:35 am

    #482209

    Use the CONVERT function, e.g.:

    

    SELECT CONVERT(char(10),GETDATE(),105)

    --Jonathan



    --Jonathan

  • November 17, 2003 at 9:28 am

    #482239

    but is there a way of changing the underlying default so I don't have to run a convert?

  • November 17, 2003 at 9:47 am

    #482245

    quote:

    but is there a way of changing the underlying default so I don't have to run a convert?

    You're just seeing the "underlying default" of whatever front-end you use, so format the value with your front-end. Dates are dates; what you see is the front-end's presentation. For example, with Query Analyzer (QA), change your workstation's regional settings to, say, Italian (or just customize the Short date format to "dd/mm/yyyy"), and then on QA's Tools|Options|Connections screen, check the Use regional settings when displaying currency, number, dates, and times option.

    --Jonathan



    --Jonathan

  • November 18, 2003 at 12:26 am

    #482363

    i used this one to convert :

    CAST(fld AS smalldatetime)

    Regards

    J

    JV


    JV

  • November 18, 2003 at 12:26 am

    #482362

    i used this one to convert :

    CAST(fld AS smalldatetime)

    Regards

    J

    JV


    JV

  • November 18, 2003 at 12:26 am

    #482361

    i used this one to convert :

    CAST(fld AS smalldatetime)

    Regards

    J

    JV


    JV

  • November 18, 2003 at 6:58 am

    #482419

    JV which one do you use again?

  • November 18, 2003 at 10:28 am

    #482476

    type 'convert' in query analyser, highlight it, an press shift and f1. To find help on the convert command, including a large table of the data-formats.

  • November 18, 2003 at 9:47 pm

    #482541

    JV,

    The nice thing about CONVERT vs CAST is that CONVERT supports a wide range of style codes as opposed to CAST.

    See the BOL for a fuller explanation, but ultimately, it matters not when doing actual data comparison.

    Quick brainteaser for our beginning DBA's: what's the quickest method to strip out the time from a DATETIME field (returning only the date)?

    Stu

    Edited by - sainswor99 on 12/05/2003 1:01:53 PM

  • November 26, 2003 at 11:44 pm

    #483864

    I think it is the easiest way

    select * from Track

    where

    Track.Track_Open_Date = CAST(CONVERT(char(10),GETDATE(),102) AS smalldatetime))

  • November 27, 2003 at 4:38 pm

    #483949

    quote:

    Quick brainteaser for our beginning DBA's: what's the quickest method to strip out the time from a DATETIME field (returning only the date)?

    Ok I'm not exactly a beginning DBA, but I'm always up for a brainteaser. Does the result need to be in datetime format, or a specific date format eg: dd-mm-yy?

    If not then you can do

    CAST(<date value> as varchar(11))

    this will return a varchar date in the format of mmm dd yyyy.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • November 27, 2003 at 6:41 pm

    #483953

    quote:

    quote:

    Quick brainteaser for our beginning DBA's: what's the quickest method to strip out the time from a DATETIME field (returning only the date)?

    Ok I'm not exactly a beginning DBA, but I'm always up for a brainteaser. Does the result need to be in datetime format, or a specific date format eg: dd-mm-yy?

    If not then you can do

    CAST(<date value> as varchar(11))

    this will return a varchar date in the format of mmm dd yyyy.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    Close enough 🙂

    I typically use CONVERT(varchar(11), @Date, [style]) instead of CAST, only because it gives me the flexibility of changing the format a bit. CAST will work just fine, however 🙂

    Stu

    Edited by - sainswor99 on 11/27/2003 6:41:59 PM

  • November 27, 2003 at 6:54 pm

    #483954

    I agree CONVERT does have the advantage of being able to change the output format. It's also handy to bear in mind that you can use CONVERT's type parameter for converting float, real, money and smallmoney.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • December 5, 2003 at 9:59 am

    #484867

    quote:

    Quick brainteaser for our beginning DBA's: what's the quickest method to strip out the time from a DATETIME field (returning only the date)?

    Stu

    Quickest performing?

    

    SUBSTRING(CAST(<datetime value> AS binary(8)),1,4)

    Quickest to type?

    

    LEFT(<datetime value>,11)

    --Jonathan (beginning DBA )



    --Jonathan

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply