Can't convert varchar(8) YYYYMMDD to date

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

Can't convert varchar(8) YYYYMMDD to date

Author

Topic

TimR
Starting Member

9 Posts

Posted - 2012-02-15 : 13:49:04

I'm having trouble converting my date field, which has a data type of varchar(8), to a date format. My guess is I need to convert it into a new column in the table but I'm unsure how to do that.

Thanks for any help!

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 13:53:03

my Guess is that you have garbage in your varchar column

Does this return anuthing?

SELECT * FROM myTable WHERE ISDATE(dteCol) = 0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TimR
Starting Member

9 Posts

Posted - 2012-02-15 : 13:57:52

Hi Brett -

It does - there are about 16 million rows of data in the table, 176 are returned with your query. Is there anyway to keep the rows but skip them while converting?

thanks,
Tim

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 14:03:33

ALTER TABLE myTable ADD NewdteCol datetime
GO

UPDATE myTable
SET NewdteCol = CONVERT(datetime,OlddteCol)
WHERE ISDATE(OlddteCol) = 1
GO

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TimR
Starting Member

9 Posts

Posted - 2012-02-15 : 14:11:54

Success! Thank you so much, I've been banging my head against the wall on this one.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 14:14:04

I accept payment in 32oz increments

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 14:15:11

I curious as to what was in the 176 rows of bad dates?

can you post some samples?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TimR
Starting Member

9 Posts

Posted - 2012-02-15 : 14:30:40

Looks like they had eight spaces.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-15 : 14:34:20

hmmm...should have convert to the "default" date..maybe they were unprintable chars

DECLARE @d varchar(8); SET @d = ' ';
SELECT CONVERT(datetime,@d)

Returns

1900-01-01 00:00:00.000

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-03-17 : 13:59:38

Check out the length too

UPDATE myTable
SET NewdteCol = CONVERT(datetime,OlddteCol)
WHERE ISDATE(OlddteCol) = 1 and len(OlddteCol)=8
GO

Want to know the reason?

SELECT isdate(2000)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources