Is the getDate function the correct function to use?

Question

Is the getDate function the correct function to use?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 1

So, this returns the expected result (I threw in the first line of the WHERE clause) with visit_date and @MinusDate displaying the exact same string for those records that you expect to match.

select convert(varchar(10),visit_date,101)as VisitDate, visit_date,@minusdate,deposit_id,sub_obs

from subjective

where sub_obs like '%morphine sulfate%'

Yet when

and visit_date = @MinusDate

is added, none of the expected records are returned.

This looks like a data type mismatch. These are varchar:

declare @MinusDate varchar(10)

convert(varchar(10),visit_date,101)

What is the data type of visit_date? If it's datetime then it includes the time as well as the date. But then, it should've displayed the time when you ran it in the SELECT statement.

Another thing to double-check would be for any char strings, but checking your postings they all look like varchar strings.

Sorry, mismatch due to data type is the only thing I'm coming up with.

Everett Wilson
ewilson10@yahoo.com

BernardS SSC Journeyman Points: 87 More actions · Answer 2

Hi Everett,

First of all, thanks for investing the time to help me out with this.

At the suggestion of a co-worker, I've gone a slightly different route. Here's the new query that seems to be working quite well.

Bernard

declare @MinusDate datetime

set @MinusDate = DATEADD (dd, -7, getdate())

select visit_date as VisitDate, deposit_id, sub_obs

from subjective

where sub_obs like '%morphine sulfate%'

and DATEDIFF (dd, @MinusDate, visit_date) = 0

hoo-t SSChampion Points: 12713 More actions · Answer 3

I just skimmed through this thread, so if this was covered, I apologise. I wonder if the problem might be with TIME rather than the date. The datetime data type contains both a date and a time, and if you attempt to compare two dates that hold different times, they will NOT compare. In other words, "9/16/2004 12:00:00am" does not equal "9/16/2004 12:00:01am". You would either need to write your query to explicitly exclude the time value, or check for a range (midnight to 11:59:59pm), unless of course, an exact time is what you're looking for.

Steve