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!!! :)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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       | 
103       | dd/mm/yy 
104       | 
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.