asked on

convert char to datetime in sql

Hello,
Due to the existing DB design, I have a column called begintime, the format is varchar(7) looks like 09:30PM and may have 9:30PM format. Another column called begindate has datetime format. I want to write a function to convert and combine these two column to a datetime format and use the result in Order By clause to compare the begindate and begintime.

thanks,

eclamshell

dacz

SELECT CONVERT(datetime, begindate , 101) + ' ' + CONVERT(datetime,begintime,108) AS BeginDateTime
FROM [table]
ORDER BY CONVERT(datetime, begindate , 101) + ' ' + CAST(begintime AS datetime)

dacz

i want o modify my post:

SELECT CONVERT(datetime, begindate , 101) + ' ' + CONVERT(datetime,begintime,108) AS BeginDateTime
FROM [table]
ORDER BY CONVERT(datetime, begindate , 101) + ' ' + CONVERT(datetime,begintime,108)

eclamshell

ASKER

but,
error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
thanks,

awking00

What DBMS?

eclamshell

ASKER

sql server 2005

Gautham Janardhan

select * from [table]
ORDER BY
cast(begindate + begintime as datetime)

eclamshell

ASKER

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

sivachirravuri

Just a example for you...
SELECT
GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;
GO

ASKER CERTIFIED SOLUTION

rk_india1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eclamshell

ASKER

However, how can I handle the formate other than 09:30PM and 9:30PM. may be an error handler to handle the rest cases. thanks,