Post reply

Format date field for data extraction

  • April 8, 2008 at 9:02 am

    #71826

    I'm trying to write a query where I need to format several date fields as MMDDYYYY. Is there a simple way to do this in T-SQL?

    I've used CONVERT(char(8), date_Field, 112) before to format a date as YYYYMMDD, but I can't find a style for MMDDYYYY.

    I've used FORMAT(date_Field, 'mmddyyyy') in Access, but I can't find a similar function in T-SQL.

    I tried using the Month(date_Field), Day(date_Field), and Year(date_Field) functions, but I can't figure out how to get the leading zeros if the month or day is one digit.

    Also, some of the date fields may contain nulls. Those need to be converted to 00000000. This query will be used to extract data from a SQL 2000 database to a text file.

    Thanks for any help on this.

    KDW

  • April 8, 2008 at 10:07 am

    #799877

    I think I found my solution. This seems to work.

    ISNULL(REPLACE(Convert(char(10), date_Field, 101), '/',''),'00000000')

  • May 15, 2008 at 9:49 am

    #816011

    Thanks for finding your own solution, 'cause you found mine too! 😉

Viewing 3 posts - 1 through 2 (of 2 total)

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