Menu
This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1.
We can compare dates using Comparison Operators in SQL like, = (Equals), < (Less than), > (Greater than), <= (Less than Equal), >= (Greater than Equal), <> (Not Equal), etc.
This is much more concise:
where
datediff(day, date1, date2) = 0
You pretty much have to keep the left side of your where clause clean. So, normally, you'd do something like:
WHERE MyDateTime >= @activityDateMidnight
AND MyDateTime < (@activityDateMidnight + 1)
(Some folks prefer DATEADD(d, 1, @activityDateMidnight) instead - but it's the same thing).
The TimeZone table complicates matter a bit though. It's a little unclear from your snippet, but it looks like t.TheDateInTable is in GMT with a Time Zone identifier, and that you're then adding the offset to compare against @activityDateMidnight - which is in local time. I'm not sure what ds.LocalTimeZone is, though.
If that's the case, then you need to get @activityDateMidnight into GMT instead.
where
year(date1) = year(date2)
and month(date1) = month(date2)
and day(date1) = day(date2)
Make sure to read Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code so that you are sure that the optimizer can utilize the index effectively when messing with dates
this will remove time component from a date for you:
select dateadd(d, datediff(d, 0, current_timestamp), 0)
Eric Z Beard:
I do store all dates in GMT. Here's the use case: something happened at 11:00 PM EST on the 1st, which is the 2nd GMT. I want to see activity for the 1st, and I am in EST so I will want to see the 11PM activity. If I just compared raw GMT datetimes, I would miss things. Each row in the report can represent an activity from a different time zone.
Right, but when you say you're interested in activity for Jan 1st 2008 EST:
SELECT @activityDateMidnight = '1/1/2008', @activityDateTZ = 'EST'
you just need to convert that to GMT (I'm ignoring the complication of querying for the day before EST goes to EDT, or vice versa):
Table: TimeZone
Fields: TimeZone, Offset
Values: EST, -4
--Multiply by -1, since we're converting EST to GMT.
--Offsets are to go from GMT to EST.
SELECT @activityGmtBegin = DATEADD(hh, Offset * -1, @activityDateMidnight)
FROM TimeZone
WHERE TimeZone = @activityDateTZ
which should give you '1/1/2008 4:00 AM'. Then, you can just search in GMT:
SELECT * FROM EventTable
WHERE
EventTime >= @activityGmtBegin --1/1/2008 4:00 AM
AND EventTime < (@activityGmtBegin + 1) --1/2/2008 4:00 AM
The event in question is stored with a GMT EventTime of 1/2/2008 3:00 AM. You don't even need the TimeZone in the EventTable (for this purpose, at least).
Since EventTime is not in a function, this is a straight index scan - which should be pretty efficient. Make EventTime your clustered index, and it'll fly. ;)
Personally, I'd have the app convert the search time into GMT before running the query.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With