Query with Date/DateTimestamp Filter not Getting All Rows

[Coder7]

Select sup_tso_id, sup_seq_nbr, created_date, end_date from supervisor
Where (('4/7/3' >= created_date) and (end_date is null))
order by sup_tso_id, sup_seq_nbr
------------------------
NOTE: create_date = datatype 'datetime' and '4/7/3' will be passed to the stored procedure as in input parameter
------------------------
This query is not returning any rows with created_date = 4/7/3 and end_date is null.

What am i doing wrong, please?

Thanks, as always.

 

[orethos]

try '4/7/03' instead of '4/7/3'

 

[Coder7]

Thanks but that returned the same rows as '4/7/3'.

 

[ClaireHCM]

how are your "created_date" column data looked like?

 

[Coder7]

created_date end_date
--------------------------- ---------------------------
2003-04-01 12:58:41.000 NULL

 

[ClaireHCM]

How about using

Where '04-07-2003 23:59:59.000' >= created_date

 

[Coder7]

Thanks but that's not the answer, Claire. The example for created_date was just to show you the datetime FORMAT.
-------------
I don't get any rows returned when the date portion of 'created_date' = the date that's in the input parameter.

What is happening, and I think it's because the database value 'created_date' contains the timestamp and the input date does not, is that when i.e. created_date = 2003-04-01 12:58:41.000 and the input parameter = '4/1/3' i only get rows starting with created date = 4/2/3 and greater. If the input parameter is 4/7/3, I only get rows with created_date = 2003-04-08 ##:##:##.### and greater (where the # represents whatever the timestamp is).

 

[SQLBill]

Are your date fields DATETIME datatype or TIMESTAMP? They are two different things.

-SQLBill

 

[Coder7]

Bill,

Datatype = DATETIME

 

[SQLBill]

I played with it a bit and found that in a datetime field, this:

4/6/3 or 2003-04-06 will ALWAYS return NULL when = is used for the reason you said...there's no time included. But > or < will always return data (if it exists).

Possible solution:

WHERE (DATEADD(day, '4/7/3',-1) >= created_date)
(you can even replace '4/7/3' with your column name or variable.

-SQLBill

 

[Coder7]

GO FIGURE....

I changed
Where ('4/7/3' >= created_date)

to
Where ( created_date >= '4/7/3' )

and the query works!!

Thanks everyone for the input!!! Have a fantastic week!

 

[Coder7]

Well I'm dead wrong......the query is not working. Claire, au contraire to what i said above, you were close. And Bill has a viable option too (THANKS EVERYONE).

Now I'm trying to find a way to drop the timestamp from both the input parameter and the database column value (or maybe change the database timestamp value to all zeros to match the timestamp of the input parameter...).

Anyone know how to do this - we're still researching over here, believe me, but we haven't come up with the answer yet.

THANKS AGAIN and have a great day all!

 

[SQLBill]

Datetime datatype MUST have both the date and time. However, either one or both can be a default. Entering just a date creates the default time of 00:00:00.000. Entering just the time creates the default date of 1/1/1900.

You could probably create a script that will change all of the times to the default.

-SQLBill

 

[Coder7]

Thanks again, Bill!

We need to conserve overhead as much as possible since this is one very small piece of a very complicated stored procedure.
------------
We found out that 00:00:00.000 is interpreted as 12:00 AM so we're adding 1 to the date that's the input parameter and it's working.

Hopefully these are not my 'famous last words' again HAHA.