How to convert date format in transact-sql

Written by daniel james
  • Share
  • Tweet
  • Share
  • Email

Converting date formats in Transact-SQL is a trivial task when using the supplied functions for datetime-to-character conversion. For nearly all standard date formats, there is an existing conversion that you can take advantage of to properly format your date. In Transact-SQL, the CONVERT function is the most commonly-used function to format dates.

Skill level:
Moderate

Other People Are Reading

Things you need

  • SQL Server 6.5 or newer (including 2000, 2005, and 2008)

Show MoreHide

Instructions

    Become Familiar with the CONVERT Function

  1. 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. 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. 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. 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())

Tips and warnings

  • There are several conversion codes for use with the CONVERT function. Try looking in the "CAST and CONVERT" documentation for one that fits your needs before using the YEAR, MONTH, DAY, and DATEPART functions to create your own.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.