How to Convert Date Format in Transact-SQL

104 12

    Become Familiar with the CONVERT Function

    • 1). The CONVERT function takes two or three variables, depending on how it is used. At its most simple invocation, the function works like this:

      CONVERT(datatype, data)

      - OR -

      CONVERT(datatype, data, code)

      For example, to convert today's date to the SQL Server's default configured date format, use the following:

      CONVERT(varchar, GETDATE())

      This will take the current local date and time, and convert it into a varchar string formatted in the SQL Server's configured locale. In the United States locale, this might be formatted, "Friday, December 25, 2009 1:35 PM."

    • 2). To format a datetime data type into a "yyyy-MM-dd" format, use the conversion code 121, as in the following example:

      CONVERT(varchar(10), GETDATE(), 121)

      This will produce a date formatted as "2009-12-25." Note the "varchar(10)" will limit the full date with time from being included in the output. If the "(10)" is omitted, the resulting string will be formatted as "2009-12-25 13:35:00.000."

    • 3). To format a datetime data type into the US standard date format, "MM/dd/yyyy," use the conversion code 101, as in the following example:

      CONVERT(varchar(10), GETDATE(), 101)

      This will produce a date formatted as "12/25/2009." Again, note the "varchar(10)" which limits the time information from being included in the result.

    • 4). If there is not an existing conversion code for the date format you wish to use, you may want to use the YEAR, MONTH, DAY, and DATEPART functions to get the individual date parts to create your own date format. For example, to produce a date formatted as "yyyy:MM-dd, hh," use the following string of functions:

      YEAR(GETDATE()) + ':' + MONTH(GETDATE()) + '-' + DAY(GETDATE()) + ', ' + DATEPART(hour, GETDATE())

Source...

Leave A Reply

Your email address will not be published.