problem sorting by date field 1

[malaygal]

I have a date field in my csv file in this format : 2:00:00 AM, 10/7/2009.
When I query the file and sort by this field asc, query did not recognize this as a data field such that 2:00:00 AM, 10/7/2009 comes before 9:00:00 PM, 10/06/2009.
I tried: order by cdate(replace([date field],',',' ')), to remove the comma and convert it to date format (which works in vbs), but replace function did not work on the query
Any help will be greatly appreciated.

 

[vicvirk]

can you provide:

1. A sample csv file
2. Your current query
3. How it is all currently tied together?

CSV files are text based, and therefore all values are considered straight text and there are no data types.

I can probably do this for you if you provide more info, it will involve getting all the data from the csv, rebuilding it so the date/time is an actual date and then filtering through to ensure you only have the data you want.



--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[malaygal]

I output all scheduled jobs from one of our server to a csv file though it has 28 data fields,I only need the fields above for demo)

WshShell.Run "Cmd.exe /c schtasks /query /S SrvrName /v /fo csv > C:\SomeDir\temp.csv"

"HostName","TaskName","Next Run Time","Status","Comment","Run As User"
"SrvrName","Job1","4:00:00 AM, 10/6/2009","","Job Desc","DomNme\USR1"
"SrvrName","Job11","2:30:00 AM, 10/7/2009","","Job Desc","DomNme\USR1"
"SrvrName","Job12","11:30:00 AM, 10/11/2009","","Job Desc","DomNme\USR1"
"SrvrName","Job2","Never","","Job Desc","DomNme\USR1"
"SrvrName","Job2","4:30:00 AM, 10/5/2009","","Job Desc","DomNme\USR1"
"SrvrName","Job3","4:30:00 AM, 10/12/2009","","Job Desc","DomNme\USR3
"SrvrName","Job3","14:30:00 AM, 10/10/2009","","Job Desc","DomNme\USR1"
"SrvrName","Job4","Never","","Job Desc","DomNme\USR2
"SrvrName","Job5","Disabled","","Job Desc","DomNme\USR1"
"SrvrName","Job6","Disabled","","Job Desc","DomNme\USR1"
"SrvrName","JOB6","4:30:00 AM, 10/14/2009","","Job Desc","DomNme\USR1"
"SrvrName","JOB7","4:30:00 AM, 10/9/2009","","Job Desc","DomNme\USR1"
"SrvrName","Job9","4:30:00 AM, 10/21/2009","","Job Desc","DomNme\USR1"
"SrvrName","Job1","Never","","Job Desc","DomNme\USR1"


I query the file created as :

strsql1 = "SELECT [TaskName],[Comment],[Next Run Time],[Status],[Run As User] FROM temp.csv where instr([Next Run Time],',') <> 0 GROUP BY [TaskName],[Comment],[Next Run Time],[Status],[Run As User] order by cdate(replace([Next Run Time],',',' ')) desc"

without the order by clause, query will work and is sorted by TaskName (default???)

Set con=CreateObject ("ADODB.Connection")

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='C:\SomeDir';" _
& "Extended Properties=""text;HDR=YES;FMT=Delimited"""

set rs = con.Execute(strsql1)

I will then output then outout the recordset to a page.

 

[guitarzan]

malaygal:
I dont think you can use Replace in a query like that... One way is to use Left/Right/Mid to extract the parts you want... not sure if there is a better way.

ORDER BY CDate(Mid([Next Run Time], InStr([Next Run Time], ',') + 1) & ' ' & Left([Next Run Time], InStr([Next Run Time], ',') - 1)) desc

 

[malaygal]

That was ingenious. Thank you.