|
| |||||||
| Sponsored Links |
 | « Previous Thread | Next Thread » |
| | LinkBack | Thread Tools | Display Modes |
|
#1
July 23rd, 2008, 01:40 AM
| |||
| |||
 MS access distinct datetime problem Hi, I have a field called order_date which date type is datetime in access so the data is in this format 08/21/2007 9:30:27 AM 08/21/2007 11:23:32 AM I only want to display 08/21/2007 can someone tell me what i am doing wrong Code: sqltr= "SELECT Distinct convert(datetime, Order_Date) from Orders order by convert(datetime, Order_Date)" Code: sqltr= "SELECT Distinct (convert(datetime,left(convert(varchar,Order_Date),10),103)) from Orders order by convert(datetime,left(convert(varchar,Order_Date),10),103)" Code: sqltr= "SELECT Distinct (CDate(format(Order_Date,'dd/mm/yyyy'))) from Orders order by CDate(format(Order_Date,'dd/mm/yyyy'))" Code: sqltr = "SELECT Distinct (Order_Date) from Orders order by Order_Date desc" Code: sqltr = "SELECT Distinct (CDate(Order_Date)) from Orders order by CDate(Order_Date) desc" Code: sqltr = "SELECT Distinct (DATE_FORMAT(Order_Date)) from Orders order by DATE_FORMAT(Order_Date) desc"  |
| Sponsored Links |
|
#2
July 23rd, 2008, 01:56 AM
| |||
| |||
| this query works Code: sqlcoaches = "SELECT Distinct (Format([Order_Date], 'mm/dd/yyyy')) from Orders order by Format([Order_Date], 'mm/dd/yyyy') desc" but when dates are displayed they are like this 10/23/1999 11/22/2001 05/2/2008 I want 05/2/2008 at top so the ordering is not working any idea todd |
|
#3
July 23rd, 2008, 09:35 AM
| ||||
| ||||
| You want to order by the original format,. this should work as far as i can see,. altough it looks like you've tried something similar Code: ORDER BY [Orders].Order_Date DESC;
__________________ If i helped you, make me famous by clicking the  Last edited by AOG123; July 23rd, 2008 at 09:38 AM. |
|
#4
July 23rd, 2008, 10:37 PM
| |||
| |||
| I get this error [Microsoft][ODBC Microsoft Access Driver] ORDER BY clause ([Orders].Order_Date) conflicts with DISTINCT. Here is my query sqltr = "SELECT Distinct (Format([Order_Date], 'mm/dd/yyyy')) from Orders ORDER BY [Orders].Order_Date DESC" any idea todd |
|
#5
July 24th, 2008, 10:00 AM
| ||||
| ||||
| You may need to use a sub-query then. The following should work: Code: Select OrdDate
From (Select Distinct Format(Order_Date, 'mm/dd/yyyy') as OrdDate
From Orders)
Order By OrdDate Desc
__________________ Wolffy ------------------------ Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary. Last edited by Wolffy; July 24th, 2008 at 10:19 AM. |
|
#9
July 24th, 2008, 10:24 AM
| ||||
| ||||
| Does it appear to be sorted as if the dates are being treated as strings? If so, it may be necessary to convert OrdDate back into the DateTime value for the ORDER BY to work. [EDIT]Yup. Had to lookup the Format function cause I don't use Access, but it DOES return a string, which will cause the ORDER BY to not work correctly[/EDIT] Last edited by Wolffy; July 24th, 2008 at 10:27 AM. |
|
#10
July 24th, 2008, 10:28 AM
| |||
| |||
| i tried it like this Code: sqlcoaches = "SELECT Distinct (Format([Order_Date], 'mm/dd/yyyy')) from Orders order BY Format([Order_Date], 'mm/dd/yyyy') DESC" |
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
