March 27, 2006 at 9:26 am
select ReservationNumber, PickupDate
from dbo.reservations
where PickupLocation IN (
select weblocid from dbo.vwRmsLocMap
)
and ReservationNumber not in (
select ReservationNumber from dbo.InternetRezToRmsLog
)
and PickupDate > '3/2/2006' --*****
order by PickupDate
Last record has a Pickup date of 2006-04-28 09:30:00.000 with above query. However if I change the date to '3/1/2006' last record is 2006-07-06 11:00:00.000
My select query is missing some data based on the date being used in comparison.
Kindly advise how to fix it.
March 27, 2006 at 9:41 am
Always use unambiguous date formats if using quoted strings and expecting SQL Server to implicitly convert your quoted string to a true datetime.
'3/1/2006' - is that 3rd of January or 1st of March ?
Use '03 Mar 2006' or use ISO format YYYYMMDD.
March 27, 2006 at 9:48 am
Expanding on PW's post, I suggest you use this format:
'YYYY-MM-DD HH:MM:SS.mmm'
So your query becomes:
...
and PickupDate > '2006-02-03 00:00:00.000'
order by PickupDate
(if you're using English date format.)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 27, 2006 at 9:48 am
Thanks, I did try using YYYYMMDD format, '2006-03-02 00:00:00.000' and the format with three digit month name but same issue is happening.
It's not just the last row but some rows are also missing in between.
Could this be an indexing issue or something related to sqlserver setup??
March 27, 2006 at 9:57 am
What is the datatype of the PickupDate field - is it datetime or varchar()?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 27, 2006 at 9:59 am
datatype of the PickupDate field is datetime
March 27, 2006 at 10:10 am
OK - this is turning into a weird one. If you take the
and PickupDate > '3/2/2006' --*****
line out of the query, what is the latest entry displayed?
You could also try running dbcc checkdb to see whether SQL Server finds any problems with your db.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 27, 2006 at 10:22 am
Use datediff function to determine the result set
DATEDIFF ( dd , '3/2/2006' ,PickupDate ) > 0
Amit Lohia
March 27, 2006 at 11:13 am
DBCC CHECKDB didn't return any errors. But DATEDIFF code provided in the last post did the trick. (It's kinda weird still, probably we need some latest patches)
Thanks everyone!!
Cheers
AJ
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply