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

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-07-06 : 12:34:09

the conversion of a char data type to a datetime data type resulted in an out-of-range

plz help me in resolving this err.

I have dates as below in varchar(255) data type i want to store this dates in datetime datatype

when I selected the min and max of all the date col i got the below results, I want to insert these dates(101 format) into new table which as datatype as datetime for these columns. however when I trying to insert I am getting the above err...

MIN(inv) MAX(inv)
1/01/1978 9/12/2008

min(ent_date) max(ent_date)
1/01/2008 9/12/2008

min(due_dt) max(due_dt)
1/01/1978 9/12/2008

Regards,
aak

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 12:56:05

post the sql that derived the data

Did you use ISDATE(col) = 1

????

They look like all valid dates




SELECT dt, ISDATE(dt) 
  FROM (
SELECT '1/01/1978' AS dt UNION ALL 
SELECT '9/12/2008' AS dt  UNION ALL 
SELECT '9/12/2008' AS dt  UNION ALL 
SELECT '1/01/2008' AS dt  UNION ALL 
SELECT '9/12/2008' AS dt  UNION ALL 
SELECT '1/01/1978' AS dt  UNION ALL 
SELECT '9/12/2008' AS dt 
) AS xxx

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

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 14:36:42

MIN ( CONVERT(DATETIME, Col1, 101) )
MAX ( CONVERT(DATETIME, COl1, 101) )

My question is, why are you using VARCHAR to store dates?

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 02:03:25

quote:
Originally posted by X002548

post the sql that derived the data

Did you use ISDATE(col) = 1

????

They look like all valid dates

SELECT dt, ISDATE(dt)
FROM (
SELECT '1/01/1978' AS dt UNION ALL
SELECT '9/12/2008' AS dt UNION ALL
SELECT '9/12/2008' AS dt UNION ALL
SELECT '1/01/2008' AS dt UNION ALL
SELECT '9/12/2008' AS dt UNION ALL
SELECT '1/01/1978' AS dt UNION ALL
SELECT '9/12/2008' AS dt
) AS xxx

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

Add yourself!
http://www.frappr.com/sqlteam

Isdate() is not fully reliable

select isdate(2000),isdate('2000'),isdate(3456234/456)

Madhivanan

Failing to plan is Planning to fail

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-07-07 : 02:11:50

The ISDATE(col) = 1
is failing as the date is some thing like this 23/11/2006

even after using convert(varchar(10),inv_date,111) I am able to run the select query however I am not able to insert this output in
datetime data type to destination table.

Regards,
aak

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-07-07 : 02:19:11

when I use convert(datetime,inv_date,111) it is giving me err saying

the conversion of the char datatype to a datetime data type resulted in an out-forange datetime value.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 05:10:51

quote:
Originally posted by aakcse

when I use convert(datetime,inv_date,111) it is giving me err saying

the conversion of the char datatype to a datetime data type resulted in an out-forange datetime value.

Which format are the dates stored in the column?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 12:34:16

why is your field storing dates declared nvarchar?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources