datetime convert to date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

datetime convert to date

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2012-02-09 : 12:35:04

I have a table where the datetime column display as
2012-02-09 03:00:04.697
i am trying to retrieve all the values for the date regardless of time.

declare @createdate datetime
SET @createdate = '2012-02-08'
select * from entitytagdatakey where createddatetime =@createdate

it is not working, what am i doing wrong?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 12:43:07

Try this:

select *
from entitytagdatakey
where createddatetime >=@createdate
and createddatetime < dateadd(day,1,@createdate)

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-09 : 12:43:28

You have to pick on the date part and ignore the time part:


select * from entitytagdatakey where CAST(createddatetime as DATE) =@createdate

A better alternative from a performance perspective couldbe

select * from entitytagdatakey where createddatetime >=@createdate and createddatetime < @createdate  + 1;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-09 : 12:44:20

quote:
Originally posted by sunitabeck

You have to pick on the date part and ignore the time part:

select * from entitytagdatakey where CAST(createddatetime as DATE) =@createdate
A better alternative from a performance perspective couldbe
select * from entitytagdatakey where createddatetime >=@createdate and createddatetime < @createdate + 1;

BY 14 SECONDS!!

sharona
Yak Posting Veteran

75 Posts

Posted - 2012-02-09 : 12:51:25

if i use
select * from entitytagdatakey where CAST(createddatetime as DATE) =@createdate
i receive the following error message
Msg 243, Level 16, State 1, Line 6
Type DATE is not a defined system type.

sharona
Yak Posting Veteran

75 Posts

Posted - 2012-02-09 : 12:52:31

this worked. thanks
select *
from entitytagdatakey
where createddatetime >=@createdate
and createddatetime < dateadd(day,1,@createdate)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 13:02:57

quote:
Originally posted by sharona

if i use
select * from entitytagdatakey where CAST(createddatetime as DATE) =@createdate
i receive the following error message
Msg 243, Level 16, State 1, Line 6
Type DATE is not a defined system type.

DATE is known since sql server 2008 and you have posted the 2008 forum so sunita was right to guess it would work

No, you're never too old to Yak'n'Roll if you're too young to die.

farrukh635
Starting Member

5 Posts

Posted - 2012-02-11 : 04:59:24

Hi,

Try this,

select * from entitytagdatakey where convert(varchar(50),createddatetime,101) =@createdate

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-11 : 08:34:01

quote:
Originally posted by farrukh635

Hi,

Try this,

select * from entitytagdatakey where convert(varchar(50),createddatetime,101) =@createdate

Would be better to use Webfred's query, especially so if there is an index on the createddatetime column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources