rocky050371
asked on
Date Query
I have seen a few examples, what is the best way of getting records between a period, is it using Between or >=, for example say I have a column called DateAdded, what is the best way of finding records added in the last 2 weeks.
Nasir Razzaq
You can use either approach. Is the column date type?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Either way works fine. But you have to beware of comparing dates with datetime. For example, '02-02-2012 0:00' is smaller than '02-02-2012 1:00'. So, if you were to do a BETWEEN '01-01-2012' AND '02-02-2012' this wouldn't show up. One way to avoid this is to do:
BETWEEN '01-01-2012 0:00' AND '02-02-2012 23:59'
Otherwise, BETWEEN and >= with <= is exactly the same thing. BETWEEN is just an easier way of doing this.
BETWEEN '01-01-2012 0:00' AND '02-02-2012 23:59'
Otherwise, BETWEEN and >= with <= is exactly the same thing. BETWEEN is just an easier way of doing this.
The DateDiff function gives you the time between two dates. Here is some code:
Public Function DaysToXmas()
Dim dtmDateNow As Date
Dim dtmXmas As Date
dtmDateNow = Date
dtmXmas = DateSerial(Year(dtmDateNow), 12, 25)
MsgBox "Days to Christmas: " & DateDiff("d", dtmDateNow, dtmXmas)
End Function
what is the datatype of the DateAdded column?
if the DateAdded column's datatype is date time (1996-08-01 00:00:00.000) then try the below query.
SELECT * FROM TABLE_1 WHERE DateAdded BETWEEN DATEADD(DAY, -14, CURRENT_TIMESTAMP) AND GETDATE()
if the above query not fits then specify the DateAdded column datatype with a sample data. i can provide another query to test
if the DateAdded column's datatype is date time (1996-08-01 00:00:00.000) then try the below query.
SELECT * FROM TABLE_1 WHERE DateAdded BETWEEN DATEADD(DAY, -14, CURRENT_TIMESTAMP) AND GETDATE()
if the above query not fits then specify the DateAdded column datatype with a sample data. i can provide another query to test