Convert varchar to date - 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 2012 Forums

Transact-SQL (2012)

Convert varchar to date

Author

Topic

DeNam
Starting Member

15 Posts

Posted - 2013-10-28 : 07:54:56

Hi,

I have the following varchar field with dates. How can i convert to a date field?

20120630
20110630
20100630
20080630
20070630
20110630
20110630
20100630
20130630
20110630

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 07:55:48

[code]
SELECT CONVERT(datetime,Column,112)
FROM Table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 08:33:04

[code]SELECT CAST(Column AS DATE);
SELECT CAST(Column AS DATETIME);[/code]

DeNam
Starting Member

15 Posts

Posted - 2013-10-28 : 08:54:13

Does not work.

The column also contains NULL values. Maybe this is the issue?

DeNam
Starting Member

15 Posts

Posted - 2013-10-28 : 08:55:51

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-28 : 09:04:29

quote:
Originally posted by DeNam

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

NULLs should not cause a problem. More likely you have some rows which are not in the expected format. Run the following - that should give you some indication/examples of rows that are causing the problem:

SELECT * FROM YourTable WHERE ISDATE(Column) = 0;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 09:05:44

it will work with NULL values


declare @t table
(
dt varchar(20)
)
insert @t
select '20120630' union all
select '20110630' union all
select '20100630' union all
select '20080630' union all
select '20070630' union all
select '20110630' union all
select '20110630' union all
select '20100630' union all
select '20130630' union all
select '20110630' union all
SELECT NULL

SELECT CONVERT(datetime,dt,112)
FROM @t


output
--------------------------------
2012-06-30 00:00:00.000
2011-06-30 00:00:00.000
2010-06-30 00:00:00.000
2008-06-30 00:00:00.000
2007-06-30 00:00:00.000
2011-06-30 00:00:00.000
2011-06-30 00:00:00.000
2010-06-30 00:00:00.000
2013-06-30 00:00:00.000
2011-06-30 00:00:00.000
NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DeNam
Starting Member

15 Posts

Posted - 2013-10-29 : 04:15:40

Thanks. Solved with the ISDATE function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-29 : 05:11:25

quote:
Originally posted by DeNam

Thanks. Solved with the ISDATE function

does that mean you'd some spurious date values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources