Convert varchar field to date field

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)

Convert varchar field to date field

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2013-12-19 : 16:49:11

I have a field named DateofBirth which is a varchar(8) field. Sample data looks like this

12192013
09232013
07271984

I'm having a hard time converting it to smalldatetime field with the error "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value"

These are some of things I've tried

SELECT
CONVERT (SMALLDATETIME, CONVERT (VARCHAR, DateofBirth,103),103)
FROM cps_new

SELECT CONVERT(SMALLDATETIME, DateofBirth, 103)
from cps_new

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-19 : 16:53:31

You've got bad data in there. Use the IsDate function to see which rows are "bad".

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 16:56:58

try this:


set dateformat mdy

select convert(smalldatetime, stuff(stuff(DateofBirth,5,0,'-'),3,0,'-'))

Be One with the Optimizer
TG

evanburen
Posting Yak Master

167 Posts

Posted - 2013-12-19 : 16:59:36

My challenge is if I do this

where isdate(dateofbirth) = 0

then every row is returned because it doesn't think the varchar(8) field is a date.

evanburen
Posting Yak Master

167 Posts

Posted - 2013-12-19 : 17:02:41

TG, thanks for the reply. I tried this

select convert(smalldatetime, stuff(stuff(DateofBirth,5,0,'-'),3,0,'-'))
FROM [dbo].[cps_new]

but still getting "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 17:14:09

That worked for me with your sample data so sounds like what Tara said is accurate. Did you try her suggestion?

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-19 : 17:16:25

Rearrange the data so that ISDATE works: yyyymmdd. Use RIGHT and LEFT functions to do that.

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 17:19:27

the SET dateformat should have taken care of that.


set dateformat mdy
select dateOfBirth
       , convert(datetime, stuff(stuff(dateOfBirth,5,0,'-'),3,0,'-'))
       , isDate(stuff(stuff(dateOfBirth,5,0,'-'),3,0,'-'))
from   (
       select '12192013' union all
       select '09232013' union all
       select '07271984'
       ) d (dateOfBirth) 

OUTPUT:

dateOfBirth                         
----------- ----------------------- -----------
12192013    2013-12-19 00:00:00.000 1
09232013    2013-09-23 00:00:00.000 1
07271984    1984-07-27 00:00:00.000 1

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-19 : 17:28:24

Gotcha, maybe the OP missed the set dateformat in your post.

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-19 : 17:33:05

yeah - maybe. He didn't post it in his response of what he tried to so maybe.

Evan,
fyi that SET command only applies to your current session so you don't need to worry about impact to other processes or your database or server. It will stay in effect until you set it back or close your query window.

Be One with the Optimizer
TG

evanburen
Posting Yak Master

167 Posts

Posted - 2013-12-19 : 17:58:46

Great, it works now. thanks to you both.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-19 : 18:27:26

SELECT
CONVERT(smalldatetime, RIGHT(DateofBirth, 4) + LEFT(DateofBirth, 4), 112)
from cps_new

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources