Re: MS SQL 2000

On 13 Jan 2005 05:47:27 -0800, Kirby wrote:

>Here is what I did before I saw your post and it works.
>
>WHERE (CONVERT(varchar(12), Acclaim.Job.JobDate, 101) <=
>CONVERT(varchar(12), DATEADD(day, 14, GETDATE()), 101)) AND
>(Acclaim.Job.JobDate > GETDATE())
>If I change it to
>
>WHERE Job.Date <= DATEADD (week, 2, Job.Jobdate)
>AND Job.Date > GETDATE()
>
>Would this accomplish the same thing? I though I read that the
>Date/Time had to be converted to a variable to calculate properly.

Hi Kirby,

Sorry to be blunt, but you didn't test your code well. It won't work; run the code below to see proof.

create table Test (JobDate datetime)
insert Test (JobDate) values ('20060101') select * from Test
WHERE (CONVERT(varchar(12), JobDate, 101) <= CONVERT(varchar(12), DATEADD(day, 14, GETDATE()), 101)) AND (JobDate > GETDATE())
go
drop table Test
go

The reason that your code doesn't work is that you convert JobDate to a character string in MM/DD/YYYY format. You also calculate two weeks from now to another character string in the same format. Then, you compare the strings - but since they are now character data, they will be compared according to character logic. The character string '01/01/2006' will appear before '01/27/2005' in an alphabetical listing!

Conversion to string AND BACK TO DATETIME!!! is sometimes used as a way to strip unwanted time portions from datetime variables. However, there are other (quicker) ways to drop or disregard the time portion of a datetime, so I only recommend converting to string for reports - and even for those, the formatting is better handled on the client.

The code you want to change your current code to won't work as well. I now see that I misread your question - you asked for current date plus two weeks, but I gave you jobdate plus two weeks. To get what you need, you should probably use   WHERE Acclaim.Job.JobDate > GETDATE()   AND Acclaim.Job.JobDate <= DATEADD (week, 2, GETDATE()) Whether this will return EXACTLY what you want depends on whether Job.JobDate contains only a date (with the time portion at the default 00:00:00.000 value) or a date plus a time - and on what exactly you do need to return in case a time portion is stored.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)