I have a column in a database (the date of the order) that is populated with a date like this:
8/21/2002 3:43:05 PM
I am trying to populate a select box with only the distinct years. Does the following sql look correct?
“SELECT DISTINCT DATEPART(year, orderDate) FROM orderTable”
That will work, but they will not be in order. This will get them in order for you:
SELECT DISTINCT DATEPART(year, orderDate) as orderdate FROM orderTable ORDER BY orderdate
Would it be possible to populate the select box with the distinct Month and Year together and then be able to query the database for the selected month/year combination? Right now it’s set up using two select boxes (one for both month and year) to where the user has to select a Month and a Year to view the reports for the month. The downside to this is that there are some months that did not sell any items so the report for that month would come back empty.
I would like them to only have to select one select box that will have the month and year value already set. This way every report they select will have data. Is this possible?
I hope that makes sense to someone
SELECT DISTINCT DATEPART(year, orderDate) as orderyear, DATEPART(month, orderdate) as ordermonth FROM orderTable ORDER BY orderdate
So now you have your year, and month. Just loop through data and build your select menu.
just a comment, brad, if you don’t mind
your query –
DATEPART(year, orderDate) as orderyear
, DATEPART(month, orderdate) as ordermonth
ORDER BY orderdate
would ordinarily not pass muster if this were any database other than mysql, which is notorious, dare i say infamous, for allowing really bad syntax to run
year and month are extracted from the table
so first, you get
and so on
next, the database has to sort all those rows into row sequence, so as to be ablt to detect and eliminate duplicates
thus, the result of the DISTINCT phase is
and so on
at this point the database is now ready to sort these results into the requested sequence as stated in the ORDER BY clause
which should be a syntax error
but it’s hardly your fault, i mean, if mysql runs it, how will you ever learn that it’s wrong?
Thanks for the info. Also, I guess I should of mentioned that I am using sql server and not mysql.
Is the DATEPART necessary?
year(orderDate) as orderyear,
month(orderdate) as ordermonth
ORDER BY orderdate
Is that not sufficient?
indeed, null, quite right, nice catch, those functions are a lot nicer than DATEPART
sadly, you’ve repeated williamsba’s error
Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
(State:37000, Native Code: 91)
So how should the order by items be set then? I’m still a little confused about that part.
year(orderdate) as orderyear
, month(orderdate) as ordermonth