datetime field in sql causing issues

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 2000 Forums

Transact-SQL (2000)

datetime field in sql causing issues

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-02-08 : 19:28:09

The following query works:

select s.created, CONVERT(CHAR(10), s.[created], 121) AS DATE
from MyDB_statistics s

But when I add the where cluase, it fails:

select s.created, CONVERT(CHAR(10), s.[created], 121) AS DATE
from MyDB_statistics s
WHERE
s.created >= '01/02/2005 12:00:00 AM' AND s.created <= '28/02/2005 11:59:59 PM' AND s.PublisherID IN ( 2 )

why is this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-08 : 19:29:08

What doesn't work though? Are you receiving an error? Not getting any data? Data returned wasn't expected?

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-08 : 19:33:40

try
WHERE
s.created >= '20050201' AND s.created < '20050301' AND s.PublisherID IN ( 2 )

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-02-08 : 21:26:24

Tara,

bad mistake, I should have pasted in the error message!

-------------
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Here is a sample output of the query WITHOUT the where cluase:

2004-05-01 00:00:00.000 2004-05-01
2004-05-01 00:00:00.000 2004-05-01
2004-05-01 00:00:00.000 2004-05-01
2004-05-02 00:00:00.000 2004-05-02
2004-05-02 00:00:00.000 2004-05-02

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-02-08 : 21:52:54

The reason I'm converting s.created to CHAR is because of the WHERE clause since it compares to a string value i.e. '01/02/2005 12:00:00 AM'

is this conversion a must? If so, should I use a larger value that CHAR 10 as it seems that might be the issue since the lenght of the string value is much larger as it includes that time and AM/PM values...

thanks for your help!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-08 : 22:28:19

Did you try the clause I posted?
The error will be comming from that clause. It's not because you are converting s.created to CHAR but because it is doing an implcict convertion of your date strings to datetime.
Probably your default format is mdy and you are giving it mdy. yyyymmdd is unambiguous and will always be converted correctly.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-02-08 : 22:34:03

ahhh...ok i'll try and change my format to yyyymmdd (i'm assuming the time part of the date is no problem!)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-08 : 22:39:04

No but if you use the clause I gave you then you don't need a time.
The clause you gave would miss anything for the last second of the last day.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-02-08 : 22:43:42

thanks nr.

actually the clause is generated via backend code so I have to do some coding to get it to work they way you have it!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-08 : 23:01:32

Put it into a stored procedure then you'll find things a lot easier.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources