Current Date Query Problem 1

[ElTech]

I'm running a query where the database has a date format of 2003-07-31 10:43:56.717

what command can I use to retrive records created 2 days ago

For example

select * from customers where createDate = 'currentdatetime'

I tried that I got a couple of errors about the date format not being correct

 

[mihaildamjan]

try this


select * from customers where createDate = '7/29/2003'

Mihail

 

[JamesLean]

Use an unambigous date format like 'yyyymmdd hh:mm:ss'. I take it the values in your column include a time portion? If so, then to find all entries created two days ago (ie, July 29th) then use:

SELECT * FROM t
WHERE datecol BETWEEN '20030729' AND '20030729 23:59:59'

--James

 

[SQLBill]

SELECT * FROM customers WHERE createdate = GETDATE()-2

GETDATE() is today
GETDATE()-1 is yesterday
GETDATE()+1 is tomorrow

You can replace the 1 with whatever number is needed.

The problem with your query is that you are comparing a STRING.

WHERE creatdate = 'currentdatetime'
createdate is a field/column
'currentdatetime' is a string
So, a date in createdate such as 2003-07-31 10:43:56.717
does not equal the word currentdatetime.

-SQLBill

 

[SQLBill]

Eltech,

JamesLean has the better solution. Mine will match the date AND time exactly.

Of course you could do this:

SELECT *
FROM customers
WHERE createdate BETWEEN CONVERT(VARCHAR(8),GETDATE()-2,120)
AND CONVERT(VARCHAR(8), GETDATE()-1,120)

This script lets you run it on a daily basis without changing the dates or times.

Basically it's:

WHERE createdate BETWEEN '2003-07-29'
AND '2003-07-30'

-SQLBill