SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert char to date problems
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rtown
Yak Posting Veteran

53 Posts

Posted - 05/14/2013 :  13:22:26  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/14/2013 :  14:55:23  Show Profile  Reply with Quote
Try this:

declare @datestr CHAR(10) =  '08/04/2013';
SELECT CONVERT(datetime, @datestr, 103);
SELECT CAST(@datestr as DATETIME);



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

Edited by - MuMu88 on 05/14/2013 14:59:32
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
410 Posts

Posted - 05/14/2013 :  15:36:45  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  00:39:23  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  00:40:51  Show Profile  Reply with Quote
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
Go to Top of Page

rtown
Yak Posting Veteran

53 Posts

Posted - 05/15/2013 :  11:00:09  Show Profile  Reply with Quote
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!
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 05/15/2013 :  11:11:29  Show Profile  Reply with Quote
--> 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000