Cursed dates!!!! - 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 2005 Forums

Transact-SQL (2005)

Cursed dates!!!!

Author

Topic

elpuerco
Starting Member

10 Posts

Posted - 2007-03-28 : 06:52:05

Could someone tell me why this fails based on the following setup please as it is driving me bonkers


INSERT INTO #T (part_no, description, pk_date)
SELECT
i.part_no,
LEFT(i.description,40),
CONVERT(datetime, ia.fld, 103)
FROM items i INNER JOIN itemsadded ia ON i.part_no = ia.part_no
WHERE CONVERT(datetime, ia.fld, 103) BETWEEN @StartDate AND COALESCE(@EndDate, ia.fld)

In #T pk_date is a datetime field
In itemsadded fld is a varchar
@StartDate and @EndDate are datetime

I keep getting the error 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value'

I have tried all manner of ways but cannot get past this error!

Any help please?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 06:53:18

Add either a

SET DATEFORMAT MDY

or
SET DATEFORMAT DMY

first in your code.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 06:56:07

[code]INSERT #T
(
part_no,
description,
pk_date
)
SELECT i.part_no,
LEFT(i.description,40),
DATEADD(DAY, DATEDIFF(DAY, 0 ,ia.fld), 0)
FROM items AS i
INNER JOIN itemsadded AS ia ON i.part_no = ia.part_no
WHERE ia.fld >= @StartDate
AND (@EndDate IS NULL OR ia.fld <= @EndDate)[/code]

Peter Larsson
Helsingborg, Sweden

elpuerco
Starting Member

10 Posts

Posted - 2007-03-28 : 09:37:51

Hi, thanks Peso.

I found too that this works!

[Code]
INSERT INTO #T (part_no, description, pk_date)
SELECT
i.part_no,
LEFT(i.description,40),
CONVERT(datetime, ia.fld, 103)
FROM items i INNER JOIN itemsadded ia ON i.part_no = ia.part_no
WHERE CONVERT(datetime, ia.fld, 103) BETWEEN @StartDate AND COALESCE(@EndDate, CONVERT(datetime, ia.fld, 103))
[/Code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 09:45:45

Great!
Good luck.

Peter Larsson
Helsingborg, Sweden

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-28 : 21:26:00

This post should have subject "Cursed database developers that use varchar when they mean dates!!!!"
Why do people do this? (appreciate it might not have been you).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-02 : 03:13:55

http://sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources