Quick date question - 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 2000 Forums

Transact-SQL (2000)

Quick date question

Author

Topic

LBrownRVa
Starting Member

3 Posts

Posted - 2007-01-02 : 16:21:22

Need to select data based on a date field, with the date value between today and one week ago. I've tried a few things that seemed logical, but either got errors or bad results. Tried finding info online but haven't yet found the same situation. Can somebody save me some time and give a quick answer?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-02 : 16:32:24

several ways. here are 2:

where datediff(d, dateField, current_timestamp) < 8

where datefield > getDate() -7

LBrownRVa
Starting Member

3 Posts

Posted - 2007-01-02 : 16:49:28

Thanks, Russell... but I have trouble with both of those. The first, I can't figure out how to fit into a syntax of WHERE datevalue < date1 and > date2 (or between/and). The second fits easily into that structure, but I get the following error: Arithmetic overflow error converting expression to data type datetime.
Msg 0, Level 11, State 0, Line 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 17:46:34

select t.* from <YourTableNameHere> AS t
WHERE t.<YourDateColumnNameHere> >= DATEADD(day, DATEDIFF(day, 7, GETDATE()), 0)
or
select t.* from <YourTableNameHere> AS t
WHERE t.<YourDateColumnNameHere> >= DATEADD(day, DATEDIFF(day, 6, GETDATE()), 0)

depending on what you mean with "one week ago"...

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-03 : 09:50:02

quote:
Originally posted by russell

several ways. here are 2:

where datediff(d, dateField, current_timestamp) < 8

where datefield > getDate() -7

It cant make use of index if it is defined on that date column.
Read Peso's reply. Thats the way to go

Madhivanan

Failing to plan is Planning to fail

LBrownRVa
Starting Member

3 Posts

Posted - 2007-01-03 : 09:57:15

Peso, "one week ago" means the value of getdate() minus 7 days.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 11:20:08

See: http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

for useful date and time functions.

Using them, you can write

where dateField between dbo.DateOnly(getdate()-7) and dbo.DateOnly(getdate())

or something like that, depending on exactly what you need.

Also -- is the column you are querying a datetime column? What is the data type? When you said you got the conversion error, that leads me to think maybe you are not using the correct data types.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 13:20:09

quote:
Originally posted by LBrownRVa

Peso, "one week ago" means the value of getdate() minus 7 days.

Yes, I wrote that in my previous answer.
Did you even try them?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources