Help with MSQuery & CAST function

  • Thread starter Thread starter Neil Evans-Mudie
  • Start date Start date
N

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
 
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.
 
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
 
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
 
Back
Top