Replace/Convert via sql while querying csv

Question

I'm trying to query a CSV via ADODB/SQL and the results need to be ordered by date. The challenge is that the date is in dd-mon-yy format. I tried to use CONVERT and REPLACE but Excel 2003 keeps throwing Automation Error at me. I've tried both of the following separately.

With CONVERT:

"SELECT CONVERT(datetime,[Business Date],106) from [filename.csv]"

With REPLACE:

"SELECT REPLACE([Business Date],'-',' ') from [filename.csv]"

I don't have control over the CSV so manually correcting the dates is not an option.

Fionnuala · Accepted Answer · 2011-10-04 13:09:59Z

1

How about:

 SELECT CDate(Right([Business Date],2) & "/" _
    & Mid([Business Date],4,3) & "/" _
    & Left([Business Date],2)) from [filename.csv]

Depending, of course, on your locale. I do not believe Convert and Replace are available to ADO.

edited Oct 4, 2011 at 13:09

answered Oct 4, 2011 at 10:01

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fasih.rana Over a year ago

You're missing the closing bracket for CDATE. The following worked for me. Thanks. SELECT CDate(Left([Business Date],2) & "/" & Mid([Business Date],4,3) & "/" & Right([Business Date],2)) from [filename.csv]

Fionnuala Over a year ago

@fasih.ahmed Another thought, with yy format you may run into problems with ambiguous dates. It may be best to add another couple of digits to the year, if you can.

fasih.rana Over a year ago

Good suggestion but this is one of those big corporations thing where getting this changed would take atleast a year. If I was controlling the data, I would format it yyyy.mm.dd because in that case sorting based on lexical order or unixtime would produce the same result.

Collectives™ on Stack Overflow

Replace/Convert via sql while querying csv

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related