Converting text to datetime value 1

[CzechNET]

The DB I have to work with has 2 fields (among other) "fldSTIME text(8)" and "fldSDATE DateTime"

Now, I need to sort the records based on the date which works fine however I'd like to sort it by the fldSTIME as well, where the fldSTIME gets converted to time and attached to the fldSDATE.
The fldSTIME has values like '10:25 AM' OR '11:05 PM'. So the final sort query would hold a valid date time value assembled from the fldsDATE and fldSTIME.

Is it even possible ? Thanks in advance.

 

[CzechNET]

Anyone ? Please

 

[r937]

it might help if you would either post in the correct forum, or else mention which database you're using



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[CzechNET]

Oh, right .. MS SQL server, though I'd preffer if it was in ANSI SQL, not T-SQL

 

[r937]

ansi sql date arithmetic won't work in sql server, i'm afraid

here's your t-sql solution --

... order by fldSDATE + fldSTIME

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[CzechNET]

Thanks r937, works great on MSSQL. Do you know of equivalent for MS Access ?

 

[r937]

... order by dateadd("s",0,fldSDATE&' '&fldSTIME)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[CzechNET]

Thanks r937 !