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

Transact-SQL (2005)

DateTime manipulations

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-14 : 18:44:51

Question:
myDate is of type DateTime and I need to select all records which are 7 or less days old

select * from table
where myDate >= DataTime.Now - 7

SA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-14 : 18:50:29

where myDate >= dateadd(day, -7, getdate())

Be One with the Optimizer
TG

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-14 : 18:54:04

what is dateadd. Its not liking that!!

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-14 : 18:55:42

error message:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '>'.

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-14 : 19:34:15

Is there something wrong i am doing. Is it working for everyone else? Just curious??

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-14 : 21:42:30

Actually its a mismatch. "myDate" is of type datetime and so we need to get the day from that and compare against DateAdd.

Is there a way to do that

SA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-14 : 21:51:15

TG's post does not work for you ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-15 : 03:31:40

it should work.
are you using sql server?
can you show full query used?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-15 : 03:38:48

I think OP meant he has a userdefined datatype myDate which is based on DATETIME.

Use

where {my mydate column name here which is based on the datetime datatype} >= dateadd(day, -7, getdate())

N 56°04'39.26"
E 12°55'05.63"

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-17 : 12:29:43

I am not sure if I understand the where statement. My column name is myDate and its of type datetime. Exmaple of myDAte values are like
8/13/2009 5:12:12 PM

Thank yo for all your input.

SA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 12:48:54

Are you using Microsoft SQL Server?
What tool do you use to execute the query?

SELECT * FROM Table1
where MyDate >= dateadd(day, -7, getdate())

N 56°04'39.26"
E 12°55'05.63"

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-17 : 12:59:25

sql server 2005

SA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 13:06:18

Do you get the error when you execute this (in a query window)?

select 'tg' where getdate() >= dateadd(day, -7, getdate())

Be One with the Optimizer
TG

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-17 : 13:22:39

select * from dbo.tableName
where getdate() >= dateadd(day, -7, getdate())

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-17 : 13:30:02

select * from dbo.tableName
where getdate() >= dateadd(day, -7, getdate())

This above select statement works fine. Now do we need to compare the day part of myDate with dateadd(day, -7, getdate()) for it to work correctly????

Thanks,
SA

SA

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-17 : 13:35:10

The last querry will return every record in the database because the current date will always be greater than the current date - 7 days.

TG's query should work.


declare @mytable table (Mydate datetime)
Insert Into @MyTable(Mydate)
select '08/19/2009' Union all
select '08/18/2009' Union all
select '08/17/2009' Union all
select '08/16/2009' Union all
select '08/15/2009' Union all
select '08/14/2009' Union all
select '08/13/2009' Union all
select '08/12/2009' Union all
select '08/11/2009' Union all
select '08/10/2009'


Select * 
from
@mytable a
where a.Mydate between dateadd(day,-7,getdate() ) and getdate()

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-17 : 13:35:11

Yes. I wonder if your original problem had a typo or an illegal character that looks like ">" 0r "=".

Confirm that [myDate] is a column in [tableName]. (I hope those are not the actual names of your table and column...)
Then take the statement that works and just replace the first "getDAte()" with [myDate].

Be One with the Optimizer
TG

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-17 : 13:44:01

I don't know what went wrong earlier but yes it works like a charm. Thank you to everyone and esp TG for being patient and helping me out. Just for future reference and benefit of users here's the query. (Needless to say these are not the actual names...)
select * from dbo.tableName
where myDate >= dateadd(day, -7, getdate())

SA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 14:10:20

quote:
Originally posted by agarwasa2008

I don't know what went wrong earlier but yes it works like a charm.

That's the beauty of Copy&Paste...

N 56°04'39.26"
E 12°55'05.63"

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-17 : 15:28:12

I agree what can I say. But thanks everyone. This is the best forum ever!!

SA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources