Post reply

Date Format

  • February 17, 2012 at 3:00 am

    #256882

    Hi All,

    select convert(varchar, getdate(), 103)

    17/02/2012

    select convert(varchar, '2012-02-17 15:13:25.060', 103)

    2012-02-17 15:13:25.060

    select convert(varchar(10), '2012-02-17 15:13:25.060', 103)

    2012-02-17

    Why there difference formatting the date to the same format(103)

    Thanks

    Guru

  • February 17, 2012 at 3:21 am

    #1448374

    varchar(10) means upto 10 only

    while

    varchar()mean varchar(max) so it gives u timeformat also !

    now got it !

  • February 17, 2012 at 3:27 am

    #1448377

    Hi kshitiz,

    But we have converted the date to format 103 then why its not formatted.

    And also when using getdate() its formatting but when we give the date its not formating

    Regards,

    Guru

  • February 17, 2012 at 3:33 am

    #1448381

    varchar does not mean varchar(max) - it means varchar(30) when CONVERTing.

    http://msdn.microsoft.com/en-us/library/ms176089.aspx

    The 103 date format means dd/mm/yyyy, so that explains the result of the first statement. The second one returns the time as well because you are converting a varchar into a varchar, so the date format parameter is ignored and you get back what you put in. The third returns dd-mm-yyyy because varchar(10) means only return the first 10 characters.

    John

    Edit - corrected typo

  • February 17, 2012 at 3:40 am

    #1448384

    Thanks John

    Regards

    Guru

  • February 17, 2012 at 4:08 am

    #1448388

    select convert(varchar, '2012-02-17 15:13:25.060', 103)

    2012-02-17 15:13:25.060

    When i convert/cast the string to date its formatting

    select convert(varchar, cast('2012-02-17 15:13:25.060' as datetime), 103)

    17/02/2012

    Regards

    Guru

  • February 20, 2012 at 9:27 am

    #1449309

    GuruGPrasad (2/17/2012)

    Hi kshitiz,

    But we have converted the date to format 103 then why its not formatted.

    And also when using getdate() its formatting but when we give the date its not formating

    Regards,

    Guru

    You're confusing what it looks like with what it is. '2012-02-17 15:13:25.060' is not a date, it's a varchar that LOOKS LIKE a date.. You're converting a VARCHAR to another VARCHAR. The formatting option is ignored when not converting from/to dates.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 20, 2012 at 9:33 am

    #1449312

    Got it,

    Thanks Drew

    Regards

    Guru

Viewing 8 posts - 1 through 7 (of 7 total)

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