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

Transact-SQL (2005)

Date Question

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2008-12-18 : 15:35:19

I'm trying to figure out how to do a date commang in TSQL

Here is the general query

SELECT [Account Number], COUNT([Account Number]) AS [Number Of Repeats], [Check In Date]
FROM vwFile1
Where *** Something like [Check In Date] BETWEEN -1 DAYS AND -30 DAYS.

I havent found how to pull the last 30 days worth of information from Yesterday and back 30 days.

In IBM I could do a convert date and then just say

Check In Date BETWEEN Current Date AND Current Date - 30 Days

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 15:49:40

You mean like

SELECT * FROM vwFile WHERE CheckInDate BETWEEN GETDATE() -30 AND GETDATE() - 1

???

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-18 : 15:50:18

WHERE Column1 >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()-30), 0)
AND Column1 < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ZMike
Posting Yak Master

110 Posts

Posted - 2008-12-18 : 15:56:21

Peso,

That's it... I must of had a syntax error. I had an extra query open having almost the same thing although I had it backwards.

I had SELECT * FROM vwFile WHERE CheckInDate BETWEEN GETDATE() -1 AND GETDATE() - 30

Thanks ! I kind of feel dumb now. I essentially had it

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-18 : 15:58:04

Keep in mind that it includes the time, so it may not be what you want. My solution gets rid of the time. Just depends on your requirements.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ZMike
Posting Yak Master

110 Posts

Posted - 2008-12-18 : 15:59:40

Tara,

Thanks for the input. We've already taken the time out in all of our date fields. :) That way we didnt have to keep doing the extra work whenever we needed stuff later on

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 00:42:04

Also with BETWEEN, you must have the lower value first and higher value last.

SELECT * FROM Table1 WHERE Col1 BETWEEN 5 AND 2

will not work

SELECT * FROM Table1 WHERE Col1 BETWEEN 2 AND 5

will work

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

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-19 : 04:22:26

Try This Also,
select * from table where CheckInDate between dateadd(m,-1,getdate()) and dateadd(d,-1,Getdate())

I Struggle For Excellence

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-19 : 04:33:15

SELECT * FROM tbl WHERE CheckInDate BETWEEN DATEADD(d,DATEDIFF(d,0,GETDATE()-30),0) AND DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources