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  
eclamshellAsked:
Who is Participating?
 
rk_india1Connect With a Mentor Commented:
as a example I placed:

Convert Char To Datetime
--------------------------------------------------------------------------------

I have columns with the following char format:

06/01/2007 12:00


To convert character data to desired style you should convert character data to date first

convert(datetime, '06/01/2007 12:00')

Then this date could be converted to a different style

convert(varchar(20), convert(datetime, '06/01/2007 12:00'), 101)

--------------------------------------------------------------------------------

0
 
daczCommented:
SELECT CONVERT(datetime, begindate , 101) + ' ' + CONVERT(datetime,begintime,108) AS BeginDateTime
FROM [table]
ORDER BY CONVERT(datetime, begindate , 101) + ' ' + CAST(begintime AS datetime)
0
 
daczCommented:
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)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
eclamshellAuthor Commented:
but,
error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
thanks,
0
 
awking00Commented:
What DBMS?
0
 
eclamshellAuthor Commented:
sql server 2005
0
 
Gautham JanardhanCommented:
select * from [table]
ORDER BY
  cast(begindate + begintime as datetime)
0
 
eclamshellAuthor Commented:
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.
0
 
sivachirravuriTeam Lead Commented:
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
0
 
eclamshellAuthor Commented:
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,
0
All Courses

From novice to tech pro — start learning today.