April 5, 2004 at 8:19 am
Please forgive my ignorance!
I have a table with a TimeStamp column on it. The timestamp column is auto-populated with getDate(). The database is set to UK dates by the way.
The timestamp dates in the table range from 01/01/2004 09:00:00 to 05/04/2004 15:00
I want to pull all the records with a timestamp between 29/03/2004 and 05/04/2004 inclusive.
SELECT *
FROM MyTable
WHERE TimeStamp BETWEEN '29/03/2004' AND '05/04/2004'
ORDER BY TimeStamp
My problem is this: The between dates are supplied by the user keying in the date range (without times) so as far as I can tell, sql server assumes this date has a time of 00:00:00, ie first thing in the morning.
This means that my query returns dates UP TO 04/04/2004, but ignores all the 05/04/2004 ones because they all have times later than 00:00:00.
There has got to be a better way than just appending 23:59:59 to my end search date so that the 05/04/2004 records would be included in the results?
Thanks for any advice.
Windows 2008 Server | SQL Server 2008
April 5, 2004 at 8:26 am
Then why use BETWEEN? Change it to:
SELECT *
FROM MyTable
WHERE TimeStamp >= '29/03/2004' AND TimeStamp <= '05/04/2004'
ORDER BY TimeStamp
Or sticking with BETWEEN, maybe you could do this:
SELECT *
FROM MyTable
WHERE CONVERT(VARCHAR(10),TimeStamp,103) BETWEEN '29/03/2004' AND '05/04/2004'
ORDER BY TimeStamp
-SQLBill
April 5, 2004 at 8:36 am
Thanks for getting back so quickly SQLBill!
I'd given those two solutions a try already, though.
The first one still misses out the 05/04/2004 records.
The second one gets screwed up over the month change. If you search between 01/03/2004 and 31/03/2004 all is well. As soon as you search from 30/03/2004 to 05/04/2004 it all goes to hell! I think it is the conversion of the date to varchar that causes the problem here.
I've just started reading about the FLOOR and CEILING functions. I'll post again if I come up with a solution.
Once again, thanks.
Windows 2008 Server | SQL Server 2008
April 5, 2004 at 11:29 am
The conversion shouldn't be causing any problems since it's converting it to dd/mm/yyyy (103 format). And the dates you are comparing it to are also strings (they are between the single quotes). What errors or results are you getting with this that are wrong?
SELECT *
FROM MyTable
WHERE CONVERT(VARCHAR(10),TimeStamp,103) BETWEEN '29/03/2004' AND '05/04/2004'
ORDER BY TimeStamp
Can you also try:
SET DATEFORMAT dmy
SELECT *
FROM MyTable
WHERE CONVERT(VARCHAR(10),TimeStamp,103) BETWEEN '29/03/2004' AND '05/04/2004'
ORDER BY TimeStamp
-SQLBill
April 5, 2004 at 11:49 am
I tried my suggestion and it worked just fine. I do have a different collation, but here's what I ran:
First I checked the amount of records....
SELECT *
FROM MyTable
WHERE mycolumn >= '2004-03-31 00:00:00' AND mycolumn <= '2004-04-02 00:00:00'
ORDER BY mycolumn
77813 records
then I tried it this way:
SELECT *
FROM MyTable
WHERE CONVERT(VARCHAR(10),mycolumn,121) BETWEEN '2004-03-31' AND '2004-04-01'
ORDER BY TimeStamp
77813 records.
-SQLBill
April 6, 2004 at 2:05 am
This is really wierd. I tried the CONVERT version of the query again and it's still not working! I know it should - I thought I was just being a bit thick!
I've tried testing it out on another table too and still no luck when I try to search over two months. I get NO results back for some strange reason, but as soon as I change by between dates to be within the same month, I get a real set of results back.
I'm gonna have to go with adding the times onto the ends of the dates until I can get a chance to go back and look at it some more.
I'll post again when I sort it so that the thread can be closed off properly.
Thanks for your effort sqlBill.
Windows 2008 Server | SQL Server 2008
April 6, 2004 at 7:17 am
Try this
SELECT *
FROM mytable
WHERE mycolumn >= '29/03/2004' AND < '06/04/2004'
April 6, 2004 at 9:03 am
This is accurate but takes a little longer to process. I use this in Business Objects so that the user does not have enter 00:00:00 23:59:59 in the query prompt.
select * from MyTable
where CAST(FLOOR(CAST(MyColumnName AS FLOAT))AS DATETIME)
between '2004-03-30' and '2004-04-05'
April 7, 2004 at 3:46 am
This may be an issue with US and UK date formats. SQL doesn't seem to like UK date formats. From what I can tell from BOL etc., SQL should take its locale from the computer it's running on. However, this doesn't always seem to be the case and US formats often prevail. (Microsoft software seems to frequently reset to US settings and force US formats in many cases.)
Working on UK locale machines, I have worked extensively with dates (especially between IIS and SQL which can be a nightmare if you're not careful), and I have always used ISO format YYYYMMDD as this sorts correctly numerically. I would therefore have run your query like this:
SELECT *
FROM MyTable
WHERE CONVERT(varchar,TimeStamp,112) BETWEEN '20040329' AND '20040405'
ORDER BY CONVERT(varchar,TimeStamp,112)
This has always worked for me.
On the date format note again, running this on SQL 2000 Developer on Win 2000 Pro with all regional settings as UK and date format set to DD/MM/YYYY:
select convert(datetime,'05/04/2004')
select convert(datetime,'31/03/2004')
select convert(datetime,'09/01/2004')
Produces this result set:
------------------------------------------------------
2004-05-04 00:00:00.000
(1 row(s) affected)
Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
------------------------------------------------------
2004-09-01 00:00:00.000
(1 row(s) affected)
Incidentally, a similar issue occurs in VBScript on IIS (which is also documented to take its regional settings from the server it's running on) unless you explicitly set Session.LCID as 2057 at the beginning of the session.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply