Dates query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Dates query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-12 : 15:15:15

How to convert the following date columns values to datetime format

SDate Sdate Sdate
------ ----------- ----------
03/04/08 12:06:50 2008-02-04 2008-02-10
03/04/08 12:06:50 2008-02-11 2008-02-17

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-12 : 15:23:31

What problem are you having with CAST/CONVERT for these?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-12 : 15:32:09

I want to convert the column values like '2008-02-10' ,'03/04/08 12:06:50' to datetime format

When i did convert it is giving gthe 'Conversion failed when converting date and/or time from character string'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-12 : 15:54:13

Those examples are working fine for me:

declare @d varchar(20)

set @d = '2008-02-10'

select CAST(@d as datetime)

set @d = '03/04/08 12:06:50'

select CAST(@d as datetime)

If you are getting that error, then it's with other values in your table. Use IsDate() function to find the culprits.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-15 : 03:36:05

Also depends on the date format you may need to use SET dateformat option

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 03:43:42

thats why we stress the need of using proper datatype for fields and also use unambiguos formats while passing date values from non datetime type fields/variables

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources