Convert char to date problems - 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)

Convert char to date problems

Author

Topic

rtown
Yak Posting Veteran

53 Posts

Posted - 2013-05-14 : 13:22:26

Hey everyone,
I have a char(10) field that I am storing a date in. All dates are entered as DD/MM/YYYY.

I need to sort a table by date but I am having problems converting my char field to date so this can happen.

I have tried the following without any success:


CONVERT(date, Returned, 103)
CONVERT(char(10), CONVERT(date, Returned, 103))
CONVERT(char(10), CAST(Returned as Datetime), 103)

Everything seems to come up with an error converting, or an out of range value. I am sure that all the values in the DB are clean and in the same format. Any ideas?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-14 : 14:55:23

Try this:
[CODE]
declare @datestr CHAR(10) = '08/04/2013';
SELECT CONVERT(datetime, @datestr, 103);
SELECT CAST(@datestr as DATETIME);
[/CODE]

BTW, many experts suggest that it is better to declare your field as a DATE or a DATETIME instead of CHAR(10)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-05-14 : 15:36:45

You have at least one value that is not a valid date.

To see the bad row(s), please run this query:

SET DATEFORMAT mdy

SELECT ...
FROM ...
WHERE
ISDATE(Returned) = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 00:39:23

quote:
Originally posted by rtown

Hey everyone,
I have a char(10) field that I am storing a date in. All dates are entered as DD/MM/YYYY.

I need to sort a table by date but I am having problems converting my char field to date so this can happen.

I have tried the following without any success:

CONVERT(date, Returned, 103)
CONVERT(char(10), CONVERT(date, Returned, 103))
CONVERT(char(10), CAST(Returned as Datetime), 103)

Everything seems to come up with an error converting, or an out of range value. I am sure that all the values in the DB are clean and in the same format. Any ideas?

see

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 00:40:51

quote:
Originally posted by ScottPletcher

You have at least one value that is not a valid date.

To see the bad row(s), please run this query:

SET DATEFORMAT mdy

SELECT ...
FROM ...
WHERE
ISDATE(Returned) = 0

ISDATE is not fully reliable for checking valid date formats

Incomplete date values like 2012 etc will also return 1 for ISDATE so there should be an additional check on length etc to make sure its a valid date value in desired format

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

rtown
Yak Posting Veteran

53 Posts

Posted - 2013-05-15 : 11:00:09

I think I know the problem from reading the replies. Some of the values will be NULL in the DB. So is this affecting the conversion? Because one of the examples I posted should indeed work.

How can I get around this?
Thanks everyone!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-15 : 11:11:29

--> some of the values will be null in DB
it is not the problem of null values, in fact there is some bad data (not valid date), which upon conversion throws the error you mentioend. The work around is to identify such data/records as suggested by Visakh/Scott, fix them, and then do the conversion.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources