Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1377
  • 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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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