SQL Date/Time BETWEEN And Comparison Operators Work With Floats
I don't want to beat a dead horse here, but I just discovered that you can interchange DATETIME and FLOAT values when using comparison operators like BETWEEN and > and <. So, if you accept that today, August 4, 2006 has the FLOAT equivalent of 38931.0, then you could query for records like this:
SELECT
s.id,
s.referer_url
FROM
web_stats_session s
WHERE
s.date_created BETWEEN 38930 AND 38932
In this case, 38930 stands for August 3, 2006 and 38932 stands for August 5, 2006. Keep in mind that the first value is inclusive (includes 08/03) and the second value is exlusive (excludes 08/05). I just think that is wicked cool.
I came across this answering a question on CF-Talk. Some guy was querying for dates but he knew that some dates would be null. I suggested this (after some testing):
SELECT
id
FROM
[table]
WHERE
[date] BETWEEN
ISNULL( date_started, 0 ) AND
ISNULL( date_ended, getDate() )
In this example, the line:
ISNULL( date_started, 0 )
... tells the query to use the database date if it exists, and if it does not exist (is null), then use the zero date. This will not limit the query on that end of the BETWEEN. The second line:
ISNULL( date_ended, getDate() )
... tells the query to use the database date if it exists, and if it does not exist (is null), then use the current date. Now that I think about it though, if the second BETWEEN value is exclusive, you might have to add one day to the getDate().
Want to use code from this post? Check out the license.
Reader Comments
For a more database-agnostic solution, try:
COALESCE(date_started,0)
The other nice thing about COALESCE (besides working on every DB platform I can think of) is that you can also do multiple fallback options:
a = COALESCE(b,c,d,e,f,g)
Rick, I have never used the COALESCE function, but it looks very cool. I have to say that ISNULL() rocked my world at the very foundation and completely changed the way I run SQL statements that have NULL values. Who knows, COALESCE might be a second revolutions.
Thanks for the hot tip.
Only problem is if the 2 floats match
Works: s.date_created BETWEEN 38930 AND 38932
Does Not: s.date_created BETWEEN 38930 AND 38930
If you convert them to an INT, the above will work.
"... the second value is exlusive (excludes 08/05). I just think that is wicked cool."
Why is that cool? It breaks BETWEEN.
Also if you want to have a range with one day, you'd have
to use 39120.0 AND 39120.9.
Using INTs rather than FLOATS resolves this, because it ignores
time in a DateTime field.
Markus,
Thanks for the tip. I give that a go.
ben
I have a problem
access database with the following SQL
" Select " & _
" EmployeeID, Date, MorningTimeIn, MorningTimeOut, " & _
" AfternoonTimeIn, AfternoonTimeOut, TotalHours " & _
" From " & _
" TimeClock " & _
" Where Date Like '%/%/%%%%'" & _
" And Date Between '" & DateT & "'" & _
" And " & _
"'" & DateF & "'" & _
" And " & _
" EmployeeID = " & "'" & EmpID & "'" & _
" Order By " & _
" Date Asc "
The problem is if DateF is 7/10/2007 & DateT is 8/4/2007 everything is fine. (I guess thats not a problem I get a proper retrieval of records)
However, if DateF is 7/9/2007 & DateT is 8/4/2007 everything goes wrong. (it retrieves record 7/9/2007 and records 8/1/2007-8/4/2007 so the problem is it is recognizing a 2 digit dayin the first ex. but when asked the look between a 1 digit day & and a 1 digit day does not recognise 2 digit days)
@Tim,
That is a very strange issue. Unfortunately, I know very little about Access. What language are you using (Not ColdFusion from the looks of it). I would say, force the day to always be two digits (ie. "09" vs "9"), but that is a ghetto hack.
Also, I don't like the looks of this: %/%/%%%%... if you are querying a "Date" field, should that always be the case? And I am not sure you need 4 % in a row for the year; I think one should suffice. Are you trying to check to see if that field is NULL?
i am facing problem when using the condition like this
this is one
create procedure P
(
@clientip varchar(50),
@Name varchar(50)
)
as
select * from tblReourse where clienip like @clientip and Name like @Name or Name is Null
here if we consider there are 6 records in the table like this
clientip Name
--------- ----------
123.2.3.4 sys
123.2.3.4 Null
123.2.3.4 www
123.2.3.4 Null
123.2.3.4 Null
here in this if i give the 'clientip' along wiht that ' Name' for the procedure it will return the corect records from the procedure
if i give only 'Name' then all the records will be returned there will be no filteringby for the particular name which we give..
i tried with isnull operator same problem exists can u plz give how to alter this procedure if i remove 'oR' condition then also we are not getting the correct answer
@Rajesh,
I have very little experience with stored procedures. But I am curious, when you say you only pass in the Name, are you passing in anything for the ClientIP? Are you passing in NULL for it?
I want to change the value of my tables column if a date column is over three days old, like deleting a reservation after three days, anyone got any ideas, am using JSP and SQL for my program.
m working in c#.when i retrieve records from access db then an exception throws that data type mismatch exception.
although i used a date field with type date/time and here in application when i write query
select * from tab wher date > '"+date1.text+"';
then grid cannot fill can any1 tell me the sol.
thanx
@shoaib,
Try outputting the SQL string rather than executing it. You might find that there is an error or unexpected value being used.
Hi.. isnull(somedate,0) would return "1900-01-01 00:00:00.000" for null.. my Q is.. how to return "-" instead?
I'm a newbie.. how do i change it to String?
@Sue,
I'm not sure you can really change data types mid-column (so to speak). For something like that, I'd just return NULL for the date and then on the programming side, check to see if the column value is a date:
<cfif isDate( query.dateColumn )> .... </cfif>
(or in whatever programming language you are using).
Thanks for your respond. I've figured out the solution.
isnull(CAST(CONVERT(varchar(11), a.icSUBDATE, 106)AS varchar(11)),'-') as SubmitDocToInsCo
it will return a dash '-' if the date is NULL..
Cheers!
Hii ben,
i have problem with comparision with date&time.i have one column which has datetime datatype. my data like "2010-11-20 12:45:50". now i have a another datetime like "2010-11-20 12:00:50". if i want to compare these in where clause then how can i do it ??
@Bhavin,
Have you tried just using a greater-than, less-than operator? Example:
WHERE dateOne > dateTwo
@ben,
yah...i tried it before your reply and it works.by the way thanks of replay...
@Bhavin,
Awesome - glad you got it working.
i have a problem when i want to select enddate -now()<=5 and enddate-now()>0 but it not work .
what could i do if want to select data from table when edate-now()lies between 0 to 5
the BETWEEN ... AND comparison operator is the equivalent of:
[expression] >= [value1] AND [expression] <= [value2]
In other words BOTH [value1] and [value2] are included in results, so I don't think this is correct:
"and the second value is exclusive"