Get date only from datetime field!

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 2005 Forums

Transact-SQL (2005)

Get date only from datetime field!

Author

Topic

BrettD
Starting Member

14 Posts

Posted - 2007-09-03 : 08:16:08

OK, I know this is really simple but I can't get my statement to pull just the date from a datetime field!

Here's my query:

select *
from tblPR
where date between convert(datetime, dateadd(day,-day(getdate())+1,getdate()),103)
and convert(datetime, dateadd(day,-day(getdate()),dateadd(month,1,getdate())),103)

I get no errors but I get the timestamp too and I only want the date.

Where am I going wrong?!

Thanks in advance,
Brett

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 08:19:26

Do you want to get data added today?

Where
datecol>=dateadd(day,datediff(day,0,getdate()),0) and
datecol<dateadd(day,datediff(day,0,getdate()),1)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-03 : 08:20:03

[code]SELECT *
FROM tblPR
WHERE [date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND [date] < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)[/code]

KH
[spoiler]Time is always against us[/spoiler]

BrettD
Starting Member

14 Posts

Posted - 2007-09-03 : 08:24:33

Thanks guys,

I'm still thinking to much into the smallest things!

Brett

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 09:34:11

quote:

Originally posted by khtan

SELECT *
FROM   tblPR
WHERE  [date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND    [date] <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

KH
[spoiler]Time is always against us[/spoiler]

or

WHERE [date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND [date] < DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) , 0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources