Help with MSQuery & CAST function

[Neil Evans-Mudie]

Guys,

Hi there. I am trying to query a text file (via MS Query) such that the
first field contains a date represented as a string (e.g. '2005-05-02
12:00:00'). I want this field to be represented in excel as datetime.

My MSQuery is like:

SELECT cast('2005-05-02 12:00:00' as date) as MyDatetime FROM
`C:\TEMP\PerfdataTest`\FspEbsApps1_AllWithHdr.csv FspEbsApps1_AllWithHdr
(via ODBC Text driver)

but returns the following error:

syntax error (missing operator) in query expression 'cast('2005-05-02
12:00:00' as date)'

I have tried replacing 'as date' with 'as sql_date'; 'as sql_c_date'; 'as
sql_timestamp'; etc, etc.

Help - please if anybody could offer some advice around MSQuery/ODBC
CAST/CONVERT function syntax I'd be immensly grateful.

--
Cheers,

Neil Evans-Mudie
-. . .. .-.. / .----. ... --- -. .. -.-. .----. / . ...- .- -.
.... -....- -- ..- -.. .. .

e: (e-mail address removed) address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieFamily/_whatsnew.msnw?&pps=k

 

[K Dales]

The format for the CAST function is to supply the variable name, not an
example of the data format; e.g.
SELECT CAST(DateTxt as Date) FROM...
What to use as the variable name depends on your source text file and
whether or not it has headers in it.

 

[Neil Evans-Mudie]

KD,

Thanks for the response - my optimism was heightened. OK I tried the
following query (where F1 is the string represented date aka '09/07/2005
12:00:00.23') with unfortunately the same error result:

SELECT CAST(Sample.F1 AS Date) FROM `C:\TEMP\PerfdataTest`\Sample.csv Sample

I'd appreciate any further ideas. Thanks.

PS Sample.csv looks like the following:
SampleDateTime
"09/07/2005 16:45:22.203"
"09/07/2005 17:45:22.218"
"09/07/2005 18:45:22.218"

--
Cheers,

Neil Evans-Mudie
-. . .. .-.. / .----. ... --- -. .. -.-. .----. / . ...- .- -.
.... -....- -- ..- -.. .. .

e: (e-mail address removed) address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieFamily/_whatsnew.msnw?&pps=k

 

[K Dales]

Is SampleDateTime the "header" (i.e. the column name) in the csv file? If
so, I think it should be as follows:
SELECT CAST(SampleDateTime AS Date) FROM `C:\TEMP\PerfdataTest`\Sample.csv
Sample