Is the getDate function the correct function to use?

Question

Post reply

Is the getDate function the correct function to use?

BernardS

SSC Journeyman

Points: 87
More actions
September 10, 2004 at 5:11 pm

#162622

Hello,
I am trying to obtain the values associated with specific dates in a table. For example, the table in question has a field called "visit_dates" and another field called "subjective". I need to get a list of the values in the "subjective" field that have a "visit_date" equal to the current date, minus 7 days. I can't get this to work using the getDate function, as it doesn't accept the argument " -7 ".
Does anyone have any suggestions?
Thanks!

Viewing 15 posts - 1 through 15 (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

sorry for not having a cleaner example but it boils down to using the DATEADD funciton.

CASE WHEN AwardDate = CONVERT(varchar(12), DATEADD(day, -6, GETDATE()), 101) THEN 1

ELSE 0 END AS AcceptInd,

Everett Wilson
ewilson10@yahoo.com

BernardS SSC Journeyman Points: 87 More actions · Answer 2

Hi Everett,

Thanks for helping me out. However, I'm afraid I don't completely undertand the query. For instance, what does the 101 represent?

Thanks!

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 3

Hello

The CONVERT function allows one to choose the date format the actual comparison takes place in.

101 is the mm/dd/yyyy format

103 is dd/mm/yyyy

Look up CONVERT on BOL for the full list.

Everett Wilson
ewilson10@yahoo.com

BernardS SSC Journeyman Points: 87 More actions · Answer 4

BernardS

SSC Journeyman

Points: 87

September 10, 2004 at 6:06 pm

#522663

Ok, Everett, will do.

Thanks again!

Brad St. Germaine SSC-Addicted Points: 481 More actions · Answer 5

Bernard,

This will also work:

select convert(varchar, getdate() - 7, 101)

To add on, depending on the size of the table, I generally refrain from using the functions within the statement. Each row that is evaluated will have to run through the functions, so if you have a 10000 rows, it will evaluate 10000 times! Set a variable to the date you are looking for, and use that in the query.

HTH, Brad

BernardS SSC Journeyman Points: 87 More actions · Answer 6

BernardS

SSC Journeyman

Points: 87

September 14, 2004 at 12:22 pm

#523032

Many thanks, Brad!

BernardS SSC Journeyman Points: 87 More actions · Answer 7

Hey guys,

Attached is my query, which still does not return the data that I know exists in visit_date field.

declare @MinusDate varchar(10)

select @MinusDate=convert(varchar,getdate() -7,101)

select deposit_id, sub_obs,visit_date

from subjective

where sub_obs like '%morphine sulfate%'

and visit_date = @MinusDate

When I run this, I don't get an error message, but rather the column headers with empty fields. Please help!

Thanks,

Bernard

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 8

Is visit_date in the same format as @MinusDate? I'm thinking if visit_date is a datetime field then you'll need to convert it to a varchar (of type 101).

Everett Wilson
ewilson10@yahoo.com

BernardS SSC Journeyman Points: 87 More actions · Answer 9

Hi Everett,

I did try that, but to no avail.

declare @MinusDate varchar(10)

select @MinusDate=convert(varchar,getdate() -7,101)

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

from subjective

where sub_obs like '%morphine sulfate%'

and visit_date = @MinusDate

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 10

Sorry, I was thinking WHERE clause. Try it there, if it doesn't work then throw the results of the converted visit_date and @MinusDate into the SELECT clasue and let us know how they're coming back.

Everett Wilson
ewilson10@yahoo.com

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 11

Try the CONVERSION in the WHERE clause. Try it there, if it doesn't work then throw the results of the converted visit_date and @MinusDate into the SELECT clasue and let us know how they're coming back.

Sorry if this is a doublepost, my browser is giving me inconsistent information.

Everett Wilson
ewilson10@yahoo.com

BernardS SSC Journeyman Points: 87 More actions · Answer 12

Hey Everett,

Excuse my ignorance, but when you say throw the results into the Select clause, I'm not sure I understand.

I have already run both as individual Select statements. In each case, the date is returned properly formatted. However, I do agree with you that the problem most likely has something to do with the conversion of the visit_date datatype.

Any other suggestions, my friend?

Thanks,

Bernard

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 13

Just curious to see what the actual strings being tested in the WHERE clause are. This can be seen by removing the date comparison for the moment and adding visit_date, @MinusDate, and convert(varchar(10),visit_date,101) to the SELECT clause in order to see what you're actually getting.

Feel free to add anything else into the SELECT statement that might be of interest.

Everett Wilson
ewilson10@yahoo.com

BernardS SSC Journeyman Points: 87 More actions · Answer 14

Ok, Everett, I did what you suggested.

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

from subjective

Everything I requested in the Select statement was correctly returned. So the problem occurs when I attempt the comparison in the the Where clause.

What can I do?

Bernard