Wednesday, February 2, 2011

Various Datetime formats in SQL Query

Problem
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Solution
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:

DATE FORMATS

Format #

Query (current date: 12/30/2006)

Sample

1

select convert(varchar, getdate(), 1)

12/30/06

2

select convert(varchar, getdate(), 2)

06.12.30

3

select convert(varchar, getdate(), 3)

30/12/06

4

select convert(varchar, getdate(), 4)

30.12.06

5

select convert(varchar, getdate(), 5)

30-12-06

6

select convert(varchar, getdate(), 6)

30 Dec 06

7

select convert(varchar, getdate(), 7)

Dec 30, 06

10

select convert(varchar, getdate(), 10)

12-30-06

11

select convert(varchar, getdate(), 11)

06/12/30

101

select convert(varchar, getdate(), 101)

12/30/2006

102

select convert(varchar, getdate(), 102)

2006.12.30

103

select convert(varchar, getdate(), 103)

30/12/2006

104

select convert(varchar, getdate(), 104)

30.12.2006

105

select convert(varchar, getdate(), 105)

30-12-2006

106

select convert(varchar, getdate(), 106)

30 Dec 2006

107

select convert(varchar, getdate(), 107)

Dec 30, 2006

110

select convert(varchar, getdate(), 110)

12-30-2006

111

select convert(varchar, getdate(), 111)

2006/12/30

TIME FORMATS

8 or 108

select convert(varchar, getdate(), 8)

00:38:54

9 or 109

select convert(varchar, getdate(), 9)

Dec 30 2006 12:38:54:840AM

14 or 114

select convert(varchar, getdate(), 14)

00:38:54:840

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement

Output

select replace(convert(varchar, getdate(),101),'/','')

12302006

select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')

12302006004426

No comments :

Post a Comment