SQL Date/Time BETWEEN And Comparison Operators Work With Floats

Posted August 4, 2006 at 2:17 PM by Ben Nadel

Tags: SQL

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"

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment