Group by date
-
I've a table where ia save the date when user have subscreibed. How can i query the number of subscriber signed in the 20 April 2006 or all the users signed since to 12 may 2006? Or between 14 april and 21 may?
if you save dates in the integer format like this: YYYYMMDD then you should be able to do something like this to get a count of subscribers on April 20, 2006: SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column = 20060420 To get a count of subscribers since May 12, 2006: SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060512 To get a count of subscribers between April 14 and May 21 of 2006: SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060414 AND Date_Column < 20060521
-
if you save dates in the integer format like this: YYYYMMDD then you should be able to do something like this to get a count of subscribers on April 20, 2006: SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column = 20060420 To get a count of subscribers since May 12, 2006: SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060512 To get a count of subscribers between April 14 and May 21 of 2006: SELECT COUNT(*) FROM Subscribe_Table WHERE Date_Column > 20060414 AND Date_Column < 20060521
-
I've a table where ia save the date when user have subscreibed. How can i query the number of subscriber signed in the 20 April 2006 or all the users signed since to 12 may 2006? Or between 14 april and 21 may?
Okay, I'm assuming you are using SQL Server, and the column is a DATETIME or SMALLDATETIME. For a single day you can use this:
SELECT * FROM MyTable WHERE [date] >= '2006-04-20 00:00:00' AND [date] <= '2006-04-20 23:59:59'
If you are only storing the date element without the time then you can do a simpler
[date] = '2006-04-20'instead. The same works for longer date ranges. e.g.SELECT * FROM MyTable WHERE [date] >= '2006-04-14 00:00:00' AND [date] <= '2006-05-21 23:59:59'
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
I've a table where ia save the date when user have subscreibed. How can i query the number of subscriber signed in the 20 April 2006 or all the users signed since to 12 may 2006? Or between 14 april and 21 may?
Does this work? on April 20th 2006
SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) = '04/20/2006'since May 12th 2006SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) >= '05/12/2006'between April 14th and May 21stSELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) BETWEEN '04/14/2006' AND '05/21/2006'---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin -
Does this work? on April 20th 2006
SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) = '04/20/2006'since May 12th 2006SELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) >= '05/12/2006'between April 14th and May 21stSELECT COUNT(*) AS totalsubscribers FROM subscribers WHERE CONVERT(VARCHAR, datesubscribed,101) BETWEEN '04/14/2006' AND '05/21/2006'---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-RollinThat would do a string based comparison, so you would be better using an ISO format where it puts YYYY-DD-MM (i.e. most significant digits first) - It also keeps it culture neutral. You don't need to convert the DATETIME or SMALLDATETIME into varchars either because a string literal will be implicity converted to a date if the format is appropriate.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
That would do a string based comparison, so you would be better using an ISO format where it puts YYYY-DD-MM (i.e. most significant digits first) - It also keeps it culture neutral. You don't need to convert the DATETIME or SMALLDATETIME into varchars either because a string literal will be implicity converted to a date if the format is appropriate.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Oh. I figured that the conversion would be necessary in order to get rid of the time portion from the datesubscribed field. In your example you provided the time: '2006-04-20 23:59:59' - but it seemed like he just wanted to compare dates. How would you do this? ---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin
-
Oh. I figured that the conversion would be necessary in order to get rid of the time portion from the datesubscribed field. In your example you provided the time: '2006-04-20 23:59:59' - but it seemed like he just wanted to compare dates. How would you do this? ---------- There go my people. I must find out where they are going so I can lead them. - Alexander Ledru-Rollin
If the
[date]column in the database contains no time elements then, say, 22-May-2006 would be stored as midnight (00:00:00). In that case you wouldn't need to do the23:59:59(or23:59on aSMALLDATETIME) thing. It has confused me why there isn't a useful built-in date only function (to extract just the date part) for comparisons such as this. Solutions I've used in the past include:-- Performance is okay
...WHERE DAY([date]) = 22 AND MONTH([date]) = 5 AND YEAR([date]) = 2006-- Performance is abysmal on large datasets and useless if part of a join (What was I thinking!?!)
WHERE [date] = dbo.datetime_rounddown(@someDateTime)-- Okay as a replacement for a column that is being SELECTed
SELECT dbo.datetime_rounddown([date]) FROM MyTable-- If using a temp table to add an extra column that specifically excluded the time portion
-- Or if the table is sufficiently large, you could add an extra column to hold the date only version
I've never tried to convert it to an varchar and use that so I don't know what the performance is like, but it would have to be the ISO format because greater/less-than comparisons wouldn't work properly, e.g. (using the British format) 22/05/2006 22/05/2007 -- Jump a year 23/05/2005 -- then jump almost two years back again. You'd get a similar situation with the US format date too. If the comparison is something that is going to be done a lot and the table is large, then it may be worth considering denormalising the data model slightly to boost the performance. I've done that before where the table was holding millions of rows. Some queries went from taking several minutes to taking a few seconds.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog