Date calculation in SQL

[Deam]

In my query I need to include all the records for a Person that fall between today and two weeks back. The table I am selecting records from has just one date field - PeriodStartDate

Appreciate any help

 

[SQLBill]

What datatype is PeriodStartDate? Is it VARCHAR or DATETIME?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

select * from YourTable
where datediff(wk,PeriodStartDate,getdate()) <= 2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Yup, that query works OK on Mondays

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Ok, what about

select * from YourTable
where datediff(dd,PeriodStartDate,getdate()) <= 14

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Deam]

It is DATETIME

 

[SQLDenis]

yep. vongrunt you are right (I even tested this code on a table)

use datediff dd instead

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Deam]

Wy do I keep on getting
Line 3: Incorrect syntax near '<'.

When I run SQL Dennis's query ??

 

[vongrunt]

Depends on definition of "two weeks back". Does it mean:

a) 14 days back, time value included
b) 14 days back, time value excluded (from 00:00AM)
c) during last two days (starting with Sunday or Monday, depending on language settings)

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

you are using SQL Server not access right?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Deam]

It is SQL server. Two weeks would be 14 days back from the time the query is run.

Basically this is a sproc that is used to create a Crystal report. The Sproc needs to pull records of employees whose PeriodStartdate falls with the two weeks of the date the report is run

Thanks !

 

[vongrunt]

Then try:

...
where PeriodStartDate >= dateadd(wk, -2, getdate()) and PeriodStartDate <= getDate()

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)