Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1374
  • Last Modified:

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  
0
eclamshell
Asked:
eclamshell
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
sivachirravuriCommented:
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
 
rk_india1Commented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now