How to cast a Varchar column to DateTime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to cast a Varchar column to DateTime

Author

Topic

Ehtesham Siddiqui
Starting Member

10 Posts

Posted - 2012-01-21 : 06:53:06

Hi,
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.


Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type 
From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID 

Where D.SchoolID = @DojoID 
and C.Days like	'%' + @Days + '%'	

Union 

Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type 
From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID 
Where D.SchoolID = @DojoID and @Date 
Between E.EventStartDate and E.EventEndDate

Please guide how can i cast it correctly

SMK
Software Engineer
India

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-21 : 07:00:41

If your ClassTime string is always in a consistent format, you may be able to use CONVERT function instead of CAST, with an appropriate style. See here for details on various styles: http://msdn.microsoft.com/en-us/library/ms187928.aspx For example if your string is 2011.12.31, which is consistent with style 102:

select CONVERT(DATETIME,'2011.12.31',102);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources