Sql - extracting distinct date

Databases
1

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”

Thanks

2

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
3

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 :slight_smile:

4

Sure would.


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.

5

just a comment, brad, if you don’t mind

your query –

SELECT DISTINCT
       DATEPART(year, orderDate) as orderyear
     , DATEPART(month, orderdate) as ordermonth
  FROM orderTable
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

2004 01
2004 01
2004 01
2004 01
2004 02
2004 02
2004 02
2004 02
2004 02
2004 02
2004 02
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

2004 01
2004 02
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?

<grin> :wink:

6

Thanks for the info. Also, I guess I should of mentioned that I am using sql server and not mysql.

7

Is the DATEPART necessary?


SELECT DISTINCT
       year(orderDate) as orderyear,
       month(orderdate) as ordermonth
  FROM orderTable
ORDER BY orderdate

Is that not sufficient?

8

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)

9

So how should the order by items be set then? I’m still a little confused about that part.

10 
select distinct
       year(orderdate) as orderyear
     , month(orderdate) as ordermonth
  from ordertable
order
    by year(orderdate)
     , month(orderdate)
11

ahhh - thanks :slight_smile: