Menu
I'm working on creating a chart for my client and they want to get the total customer count over a 24 hour, 3 day, 1 week, 1 month, etc period. I'm honestly not the best with SQL, so generating these queries aren't my forte.
In regards to getting the customers over 24 hours, I've come across two "where" statements that may work, but I'm not sure which is the best.
First version:
WHERE DATEDIFF(hh,CreatedDate,GETDATE())>24
Second Version:
WHERE CreatedDate >= DATEADD(HH, -24, GETDATE())
The first version generates 21 rows and the second generates 17 rows (from the same dataset, of course) so obviously one is more accurate than the other. I'm leaning towards the first, but I would like your opinion... please.
Thanks, Andrew
842
If you want to select the last 24 hours from a datetime field, substitute 'curate()' with 'now()'. This also includes the time.
SELECT * FROM product WHERE pdate >= DATEADD(day, -30, getdate()).
SELECT DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 WHEN 1 THEN -2 WHEN 2 THEN -3 ELSE -1 END, DATEDIFF(DAY, 0, GETDATE())); This will work for all language and DATEFIRST settings.
If you are using mySql or similar SQL engines then you can use the DATEADD method to add hour, date, month, year to a date. select dateadd(hour, 5, now()); If you are using postgreSQL you can use the interval option to add values to the date.
Avoid the first version. First, because it disables index utilization. The second (functional) issue with the first version is, DATEDIFF(HOUR...) returns all values less than 25 hours. Try this for clarity:
SELECT DATEDIFF(HOUR, '2010-07-19 00:00:00', '2010-07-20 00:59:59.99')
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
