Post reply

long date format

  • April 7, 2011 at 5:08 pm

    #234418

    How do you format a field to return a long date format ie January 1, 2011?

  • April 7, 2011 at 5:12 pm

    #1308897

    Like this:

    DECLARE @Date DATETIME = GETDATE()

    SELECT CONVERT(VARCHAR(50),@Date,107) DayInWords

  • April 7, 2011 at 5:18 pm

    #1308901

    I tried it this way and got Jan 1, 2011.

    SELECT CONVERT(VARCHAR(50),mt.hiredate,107) FROM MyTable mt

  • April 7, 2011 at 5:27 pm

    #1308902

    isnt that what you wanted?

  • April 7, 2011 at 5:30 pm

    #1308905

    I need the month spelled out like January.

  • April 7, 2011 at 5:35 pm

    #1308906

    Try this then:

    DECLARE @Date DATETIME = GETDATE()

    SELECT CASE LEFT( CONVERT(VARCHAR(50),@Date,107),3)

    WHEN 'Jan' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Jan' ,'January')

    WHEN 'Feb' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Feb' ,'February')

    WHEN 'Mar' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Mar' ,'March')

    WHEN 'Apr' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Apr' ,'April')

    WHEN 'May' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'May' ,'May')

    WHEN 'Jun' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Jun' ,'June')

    WHEN 'Jul' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Jul' ,'July')

    WHEN 'Aug' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Aug' ,'August')

    WHEN 'Sep' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Sep' ,'Sepetember')

    WHEN 'Oct' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Oct' ,'October')

    WHEN 'Nov' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Nov' ,'November')

    WHEN 'Dec' THEN REPLACE( CONVERT(VARCHAR(50),@Date,107) ,'Dec' ,'December')

    END AS LongDate

  • April 7, 2011 at 5:39 pm

    #1308909

    Thank you 😀

  • April 8, 2011 at 10:48 am

    #1309300

    Probably not important, but you can also make sure of the DATENAME function, that way sql will handle localizing the name of the month is that should ever be needed.

  • April 8, 2011 at 3:11 pm

    #1309445

    Lamprey13 (4/8/2011)

    Probably not important, but you can also make sure of the DATENAME function, that way sql will handle localizing the name of the month is that should ever be needed.

    I like it. Much simpler. Thanks for pointing it out.

    DECLARE @Date DATETIME = GETDATE() ;

    SELECT DATENAME(MONTH, @Date) + ' ' +

    DATENAME(DAY, @Date) + ', ' +

    DATENAME(YEAR, @Date) ;

    April 8, 2011

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 8, 2011 at 9:53 pm

    #1309514

    😎

Viewing 10 posts - 1 through 9 (of 9 total)

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