How to convert SQL Server's timestamp column to DateTime format

How to get different SQL Server date formats

1. Use the date format option along with CONVERT function.
2. To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23)
3. To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1)
4. Check out the chart to get a list of all format options.

Using cast you can get a date from a timestamp field:

SELECT CAST(timestamp_field AS DATE) FROM tbl_name

My coworkers helped me with this:

select CONVERT(VARCHAR(10), <tms_column>, 112), count(*)
from table where <tms_column> > '2012-09-10'
group by CONVERT(VARCHAR(10), <tms_column>, 112);

select CONVERT(DATE, <tms_column>, 112), count(*)
from table where <tms_column> > '2012-09-10'
group by CONVERT(DATE, <tms_column>, 112);

"You keep using that word. I do not think it means what you think it means." — Inigo Montoya

The timestamp has absolutely no relationship to time as marc_s originally said.
declare @Test table (
     TestId int identity(1,1) primary key clustered
    ,Ts     timestamp
    ,CurrentDt datetime default getdate()
    ,Something varchar(max)
)

insert into @Test (Something)
    select name from sys.tables
waitfor delay '00:00:10'

insert into @Test (Something)
    select name from sys.tables

select * from @Test

Notice in the output that Ts (hex) increments by one for each record, but the actual time has a gap of 10 seconds. If it were related to time then there would be a gap in the timestamp to correspond with the difference in the time.

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.

How to get different SQL Server date formats

Use the date format option along with CONVERT function
To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23)
To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1)
Check out the chart to get a list of all format options


Below is a list of formats and an example of the output.  The date used for all of these examples is "2006-12-30 00:38:54.840".

DATE ONLY FORMATS
Format #
Query
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
12
select convert(varchar, getdate(), 12)
061230
23
select convert(varchar, getdate(), 23)
2006-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
112
select convert(varchar, getdate(), 112)
20061230

TIME ONLY FORMATS
8
select convert(varchar, getdate(), 8)
00:38:54
14
select convert(varchar, getdate(), 14)
00:38:54:840
24
select convert(varchar, getdate(), 24)
00:38:54
108
select convert(varchar, getdate(), 108)
00:38:54
114
select convert(varchar, getdate(), 114)
00:38:54:840

DATE & TIME FORMATS
0
select convert(varchar, getdate(), 0)
Dec 12 2006 12:38AM
9
select convert(varchar, getdate(), 9)
Dec 30 2006 12:38:54:840AM
13
select convert(varchar, getdate(), 13)
30 Dec 2006 00:38:54:840AM
20
select convert(varchar, getdate(), 20)
2006-12-30 00:38:54
21
select convert(varchar, getdate(), 21)
2006-12-30 00:38:54.840
22
select convert(varchar, getdate(), 22)
12/30/06 12:38:54 AM
25
select convert(varchar, getdate(), 25)
2006-12-30 00:38:54.840
100
select convert(varchar, getdate(), 100)
Dec 30 2006 12:38AM
109
select convert(varchar, getdate(), 109)
Dec 30 2006 12:38:54:840AM
113
select convert(varchar, getdate(), 113)
30 Dec 2006 00:38:54:840
120
select convert(varchar, getdate(), 120)
2006-12-30 00:38:54
121
select convert(varchar, getdate(), 121)
2006-12-30 00:38:54.840
126
select convert(varchar, getdate(), 126)
2006-12-30T00:38:54.840
127
select convert(varchar, getdate(), 127)
2006-12-30T00:38:54.840

ISLAMIC CALENDAR DATES
130
select convert(nvarchar, getdate(), 130)

131
select convert(nvarchar, getdate(), 131)
10/12/1427 12:38:54:840AM


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


If you want to get a list of all valid date and time formats, you could use the code below and change the @date to GETDATE() or any other date you want to use.  This will output just the valid formats.

DECLARE @counter INT = 0
DECLARE @date DATETIME = '2006-12-30 00:38:54.840'

CREATE TABLE #dateFormats (dateFormatOption int, dateOutput nvarchar(40))

WHILE (@counter <= 150 )
BEGIN
   BEGIN TRY
      INSERT INTO #dateFormats
      SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 150
      BEGIN
         BREAK
      END
   END CATCH
END

SELECT * FROM #dateFormats


Recommended Reading

Continue your learning on SQL Server dates with these tips:

Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions
SQL Server Date and Time Data Types
SQL Server function to convert integer date to datetime format
SQL Server DateTime Best Practices
Format SQL Server Dates with FORMAT Function


Next Steps

The formats listed above are not inclusive of all formats provided. Experiment with the different format numbers to see what others are available.
These formats can be used for all date/time functions, as well as data being served to clients, so experiment with these data format conversions to see if they can provide data more efficiently.
Also, check out the SQL Server FORMAT Function to Format Dates.