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-16 : 02:34:47

data field is a varachar looks like 531112-0330, where the first six characters form a date.

I want to convert to a date field.

How should i do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-16 : 02:35:55

How does 531112 convert to a date?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 02:52:42

is it julian date?
http://visakhm.blogspot.in/2012/05/julian-to-iso-date-conversion-function.html

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

DeNam
Starting Member

15 Posts

Posted - 2013-10-16 : 03:31:46

My datafield is named person_number and the value is 531112-0330.
Data type is varchar(15).

So I am doing the following:

select convert(date, '19' + LEFT(person_number,6), 109) from dbo.Table1

The result I want is 1953-11-12 and datafield: Date.

I get the following message: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

At the same time, this works:

Declare @date varchar(100)
set @date = '20100320'
select CONVERT(date, @date,109).

What am i doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 03:39:01

try this


select convert(date, case when LEFT(person_number,2)>= 50 THEN '19' ELSE '20' END + LEFT(person_number,6), 109) from dbo.Table1 WHERE LEN(LEFT(person_number,CHARINDEX('-',person_number)-1))=6

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

DeNam
Starting Member

15 Posts

Posted - 2013-10-17 : 08:29:23

Does not work.

Following message:

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

Tried to convert the varchar to numeric before converting to date. Did not work either.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 10:04:36

that means you've some data which is not having valid date values


select convert(date, case when LEFT(person_number,2)>= 50 THEN '19' ELSE '20' END + LEFT(person_number,6), 109) from dbo.Table1 WHERE LEN(LEFT(person_number,CHARINDEX('-',person_number)-1))=6 AND ISDATE(LEFT(person_number,CHARINDEX('-',person_number)-1))=1

------------------------------------------------------------------------------------------------------
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