We help IT Professionals succeed at work.

MSSQL 2005 - DateTime


declare @date datetime
set @date = '2008-01-29 12:01:09.437'

How can I cast @date so it will not contain any time, only contain the date:

Many thanks!!! :)
Watch Question

Database Consultant
Top Expert 2009
Also please notice that the format you used to put the date and time into a variable is not the best and is dependent on session/connection settings. Try the following:

set language polish
declare @date datetime
set @date = '2008-01-29 12:01:09.437'

You will get 242 error because some languages (Polish for example) will treat this format as YYYY-DD-MM...

Use the following instead:

set language polish
declare @date datetime
set @date = '2008-01-29T12:01:09.437'
select @date

If you are not going to provide the time use 'YYYYMMDD' format (do not use the dash as a separator).
yep, the number aneeshattingal uses at the end determines date format. Below is a list of other formats you can use with the CONVERT feature.
Style ID | Format Type
0 or 100  | mon dd yyyy hh:miAM (or PM) 
101       | mm/dd/yy 
102       | yy.mm.dd 
103       | dd/mm/yy 
104       | dd.mm.yy 
105       | dd-mm-yy 
106       | dd mon yy 
107       | Mon dd, yy 
108       | hh:mm:ss 
9 or 109  | mon dd yyyy hh:mi:ss:mmmAM (or PM) 
110       | mm-dd-yy 
111       | yy/mm/dd 
112       | yymmdd 
13 or 113 | dd mon yyyy hh:mm:ss:mmm(24h) 
114       | hh:mi:ss:mmm(24h) 
20 or 120 | yyyy-mm-dd hh:mi:ss(24h) 
21 or 121 | yyyy-mm-dd hh:mi:ss.mmm(24h) 
126       | yyyy-mm-dd Thh:mm:ss.mmm(no spaces) 
130       | dd mon yyyy hh:mi:ss:mmmAM 
131       | dd/mm/yy hh:mi:ss:mmmAM 

Open in new window

In SQL Server 2005 there is no date-only data type.
In SQL Server 2008 you can use the DATE type.

If you just want to display a datetime value without a time then do that in your client application or presentation tier. SQL Server does not and cannot control the display formatting of a datetime value.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.