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

General SQL Server Forums

New to SQL Server Programming

date format

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-22 : 10:58:39

Hi, I already convert the format to date but still getting this error:

Conversion failed when converting date and/or time from character string.

Select convert(date,createdDate) Date .....

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-04-22 : 11:04:08

What is the input CreatedDate , could you post . Here is an example of a data convert
Select CONVERT(Date, '13-5-2012', 101)

Read more on msdn http://msdn.microsoft.com/en-us/library/ms187928.aspx

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-22 : 11:08:27

the input date is

2011-12-31

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 11:12:03

It may be that there are some rows in your table that are invalid. You can find them using this:

select * from yourTable where isdate(createdDate) = 0;

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-22 : 11:24:09

when i run select manually it works but it doesn't works when i union all together.

Select *
From (
Select * From #0
UNION ALL
Select * From #1
UNION ALL
Select * From #2
) A

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-04-22 : 11:24:37

you could think about changing the dateformat
select CONVERT(Date, '2011-12-31', 101)

to the ISO format

select CONVERT(Date, '20111231', 101)

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources