Problem with date comparison - 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)

Problem with date comparison

Author

Topic

akaii
Starting Member

4 Posts

Posted - 2009-01-19 : 22:31:25

Hi,

I get an error while processing this request on my vb.net aspx website.

SELECT RptEventTypes.EventText, RptEvents.EventId,
RptEvents.DateTime, RptEvents.IndividualNdx, RptEvents.FloatValue, 
RptIndividuals.IndividualNdx, RptIndividuals.LastName, 
RptIndividuals.FirstName FROM InetDb.dbo.RptEventTypes RptEventTypes, 
InetDb.dbo.RptEvents RptEvents, InetDb.dbo.RptIndividuals 
RptIndividuals 
WHERE RptEventTypes.EventTypeId = 
RptEvents.EventTypeNdx AND RptEvents.TenantNdx = 
RptIndividuals.TenantNdx AND RptEvents.IndividualNdx = 
RptIndividuals.IndividualNdx AND RptEvents.FloatValue > 0. AND 
(RptEventTypes.EventText = 'Reader entry' OR RptEventTypes.EventText 
= 'Reader exit') AND RptEvents.IndividualNdx = '145' AND 
RptEvents.DateTime BETWEEN 19/01/2009 00:00:00 AND 20/01/2009 
00:00:00 
ORDER BY RptEvents.IndividualNdx ASC, RptEvents.EventId 
DESC, RptEventTypes.EventText ASC, RptIndividuals.LastName ASC

I'm getting the following error message:
Incorrect syntax near '00'.

I guess it must be a problem of date format but I need your advices about that.

Thanks

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-19 : 22:41:55

U change in the where condition,

RptEvents.DateTime BETWEEN '01/19/2009 00:00:00' AND '01/20/2009 00:00:00'

akaii
Starting Member

4 Posts

Posted - 2009-01-19 : 23:58:57

Ok u were right but now I get another error message :

The conversion of a char data type to a datetime data type resulted in an out-of range datetime value.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-20 : 00:00:01

change the datefield to this format:
RptEvents.DateTime BETWEEN '01/19/2009 00:00:00' AND '01/20/2009 00:00:00'

akaii
Starting Member

4 Posts

Posted - 2009-01-20 : 01:43:28

it's done and then I get the other error message :
The conversion of a char data type to a datetime data type resulted in an out-of range datetime value.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-20 : 01:52:04

If u put the condition RptEvents.DateTime BETWEEN 19/01/2009 00:00:00 AND 20/01/2009 00:00:00 , then error will come ( The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.),

so, U can change the format like this, then we will get correct one
between '01/19/2009 00:00:00' AND '01/20/2009 00:00:00'

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-20 : 01:55:16

Or try this also,
datefield between convert(varchar(32),'01/19/2009 00:00:00',100)ANDconvert(varchar(32),'01/20/2009 00:00:00',100)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-20 : 02:02:34

use mm/dd/yyyy format
so that ur getting the error out-of-range datetime value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 02:17:12

to avoid this type of error, always use universal date format yyyy-mm-dd hh:mm:ss so it should be RptEvents.DateTime BETWEEN '2009-01-19 00:00:00' AND '2009-01-20 00:00:00'

kongaung
Starting Member

8 Posts

Posted - 2009-01-20 : 04:39:00

hello
Test the follow

RptEvents.DateTime BETWEEN '19/01/2009 00:00:00' AND '20/01/2009 00:00:00'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:54:34

quote:
Originally posted by kongaung

hello
Test the follow

RptEvents.DateTime BETWEEN '19/01/2009 00:00:00' AND '20/01/2009 00:00:00'

this will still break if it interprets this in mm/dd/yyyy format

akaii
Starting Member

4 Posts

Posted - 2009-01-20 : 17:44:34

quote:
Originally posted by bklr

use mm/dd/yyyy format
so that ur getting the error out-of-range datetime value.

do I use this in the sql or the vb.net code?

I'm new... thanks for your patience

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-20 : 23:23:59

quote:
Originally posted by akaii

quote:
Originally posted by bklr

use mm/dd/yyyy format
so that ur getting the error out-of-range datetime value.

do I use this in the sql or the vb.net code?

I'm new... thanks for your patience

u can use that format in ur code in sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:12:28

quote:
Originally posted by bklr

quote:
Originally posted by akaii

quote:
Originally posted by bklr

use mm/dd/yyyy format
so that ur getting the error out-of-range datetime value.

do I use this in the sql or the vb.net code?

I'm new... thanks for your patience

u can use that format in ur code in sql

thats a good approach as long as you cant guarantee the format in which date value is input will be consistent
so better to always stick to universal format

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources