Unable to use BETWEEN with date value!
-
SQL Server 7.0:
I have a table where a particular date column , when_dt, has a default value of GetDate() set on it, so that whenever an insert is made to the table, this particular column acts as a datetime stamp.
The problem is when I run a simple query on the table like
SELECT * from table
WHERE when_dt ='03/03/2003'
I do not get any results, though there are rows with values like '2003-03-03 05:24:21.810' in the table. The curious thing is that a query with
....WHERE when_dt > '03/02/2003' will work.
So the < and > operators work but not the = operator.
I did work around the problem using datepart, which is tedious, because I have to check the day, month and year in 3 separate statements,but I find it baffling that it should'nt work. Also BETWEEN does not work where < and > do! Can somebody throw some light on this?
On a side note, does any of you think like me that SQL server should have a function to extract just the whole date part from a datetime value?
-
The value '03/03/2003' is interpreted as midnight. So if you are passing this into a stored procedure use
when_dt >= @Date and when_dt < @Date + 1
(or use the Dateadd function)
You can convert the date to a varchar "CONVERT(VARCHAR, @date, 111)" with the 111 format and that will chop it, but I wouldn't recommend doing your comparison like that.
-
The date of '03/02/2003' is it equal to '2 Feb' or the '2 Mar'
Try WHERE when_dt > Cast('2 Feb 2003' as datetime)
-
Although this was tested on SQL 2000, I think it will work on SQL 7 (don't have one here to test with):
select * from orders
where orderdate between '1996-07-19 00:00:00.000' AND '1996-07-19 23:59:59.999'
Returns all orders placed on the 19th of July, 1996 (I use Swedish date format).
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
The interpretation of '03/02/2003' depends on your server language settings. BOL say to use yyyymmdd format to ensure the same interpretation across all languages.
Jay Madren
Jay Madren -
CLARIFICATION:
Thanks to the folks who have answered till now! The problem still remains-
Here are some clarifications-
-The settings on my database are US English and date format - so '03/26/2003/ means
26 march 2003. Please replace the original '03/03/2003' with '03/26/2003' which is a better value.
- chrhedga's idea cannot be implemented as the query is being generated in a coldfusion page- ans the '03/26/2003' value comes in from a form field.
- 5409045121009 missed the point- I cannot do an "=" or "BETWEEN". As I explained earlier, < and > operators work fine. Thanks anyway..
- cheddar: I don't know what you are talking about! Thanks anyway...
-
Cheddar's suggestion of CONVERT(VARCHAR, @date, 111) will do what you want. The statement would be:
SELECT * from table
WHERE CONVERT(VARCHAR, when_dt, 101) = '03/26/2003'
I changed the style parameter to 101 for US standards. Cheddar's warning was that this can cause the query to take longer with large numbers of rows, but I don't think it will be significant.
Jay Madren
Jay Madren -
Another thing to note: The method I just posted compares the vaules as text strings. Don't use this method with between or < & > comparisons. Just use the date as is for those.
Jay Madren
Jay Madren -
jmadren ---- thanks a lot!
-
quote:
The problem is when I run a simple query on the table likeSELECT * from table
WHERE when_dt ='03/03/2003'
Is that the full SQL statement you are trying to run?
quote:
I do not get any results, though there are rows with values like '2003-03-03 05:24:21.810' in the table. The curious thing is that a query with....WHERE when_dt > '03/02/2003' will work.
As previously stated, that is because SQL doesn't store the date by itself. It stores it as date and time. so '03/02/2003' is actually '03/02/2003 00:00:00.000', which of course does not equal '03/02/2003, therefore no records are returned.
quote:
So the < and > operators work but not the = operator.I did work around the problem using datepart, which is tedious, because I have to check the day, month and year in 3 separate statements,but I find it baffling that it should'nt work. Also BETWEEN does not work where < and > do! Can somebody throw some light on this?
Not sure why you're getting that problem. I don't have any issues with between, as shown below.
Original Resultset
AU2003-03-13 15:12:52.120
AU2003-03-14 10:38:51.470
AU2003-03-14 10:43:34.640
AU2003-03-14 13:57:21.690
AU2003-03-14 14:33:50.160
AU2003-03-24 10:04:39.690
AU2003-03-13 15:54:57.917
AU2003-03-13 16:22:33.103
AU2003-03-13 17:21:53.227
AU2003-03-14 14:18:13.110
DEclare @Rdate datetime
Set @RDate = '13 Mar 2003'
SELECT C_CNTRY, D_RUN_STRT
FROM dev_smb_datamart.dbo.ETL_RunLog
WHERE D_RUN_STRT > @rdate and D_RUN_STRT < @rDate + 1
Results returned
----------------
AU2003-03-13 15:12:52.120
AU2003-03-13 15:54:57.917
AU2003-03-13 16:22:33.103
AU2003-03-13 17:21:53.227
SELECT C_CNTRY, D_RUN_STRT
FROM dev_smb_datamart.dbo.ETL_RunLog
WHERE D_RUN_STRT Between @rdate and @rDate + 1
Results returned
----------------
AU2003-03-13 15:12:52.120
AU2003-03-13 15:54:57.917
AU2003-03-13 16:22:33.103
AU2003-03-13 17:21:53.227quote:
On a side note, does any of you think like me that SQL server should have a function to extract just the whole date part from a datetime value?It does. Check CONVERT in BOL, specifically the use of the style parameter.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 03/26/2003 3:50:07 PM
--------------------
Colt 45 - the original point and click interface
-
A comment on CASTing a text value into a date datatype:
It is always better to use CONVERT instead of CAST for explicit conversion, especially in the international environment. Please see below:
/* -- cut here -- */
SET NOCOUNT ON
SET DATEFORMAT YMD
-- returns 2003-03-02 (with no convert style)
SELECT 'correct date', CAST('03/02/2003' AS DATETIME)
-- returns 2003-03-02 (with convert style)
SELECT 'correct date', CONVERT(DATETIME, '03/02/2003', 101)
SET DATEFORMAT YDM
-- returns 2003-02-03 (again with no convert style)
SELECT 'incorrect date', CAST('03/02/2003' AS DATETIME)
-- returns 2003-03-02 (with convert style)
SELECT 'correct date', CONVERT(DATETIME, '03/02/2003', 101)
SET NOCOUNT OFF
/* -- cut here -- */
Sincerely,
Billy
-
quote:
- chrhedga's idea cannot be implemented as the query is being generated in a coldfusion page- ans the '03/26/2003' value comes in from a form field.I have no idea how ColdFusion works, but I guess you do some kind of coding to create the SQL statement with the value from the form field. Why can't you then just add the time parts to the value from the form field and create a query like the one I suggested?
The problem with a query such as the one suggested by jmadren (quoted below) is that the where-clause is not a SARG, and therefore the optimizer will have a hard time using an index to find the specific rows to return.
quote:
SELECT * from tableWHERE CONVERT(VARCHAR, when_dt, 101) = '03/26/2003'
SARGs should be in this format: column Operator [column | constant | expression]
The query above would probably need a full scan even if there is an index on the column when_dt.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply