What's wrong with my Datetime convert? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What's wrong with my Datetime convert?

Author

Topic

titanotam
Starting Member

6 Posts

Posted - 2009-07-10 : 01:29:00

In my table JOBS, there are END_DATE (datetime) and Job (String) fields with values: 11/07/2009 - JobA and 12/07/2009 - JobB

GETDATE() = 10/07/2009

here is my T-SQL:
------------
SELECT Job

FROM JOBS

WHERE CAST (CONVERT(VARCHAR(10),END_DATE,112) AS INTEGER) - CAST(CONVERT(VARCHAR(10),GETDATE(),112) AS INTEGER) <= @days
and CAST (CONVERT(VARCHAR(10),END_DATE,112) AS INTEGER) - CAST(CONVERT(VARCHAR(10),GETDATE(),112) AS INTEGER) >= 0
------------

when @days = '2' , it return JobB
but when @days = '1' , it doesn't return JobA.

What's wrong with my code?
Please help me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 01:40:15

You don't have to convert the END_DATE to string then to integer. Just find the required end date from getdate()


select @days = 1 -- use integer not string '1'

select Job
from   JOBS
where  END_DATE >= dateadd(day, datediff(day, 0, getdate()), @days)

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-10 : 01:53:15

select job from jobs where datediff(d,end_Date,getdate()) <= @date and datediff(d,end_Date,getdate()) >0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-10 : 02:28:06

quote:
Originally posted by bklr

select job from jobs where datediff(d,end_Date,getdate()) <= @date and datediff(d,end_Date,getdate()) >0

this "datediff(d,end_Date,getdate())" will not allow SQL Server to utilize any index available on end_date and will result in table scan.

the "datediff(d,end_Date,getdate()) >0" is just to ensure that END_DATE is greater than current date. This will be redundant with

END_DATE >= dateadd(day, datediff(day, 0, getdate()), @days)

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 02:56:34

quote:
Originally posted by khtan

the "datediff(d,end_Date,getdate()) >0" is just to ensure that END_DATE is greater than current date. This will be redundant with

END_DATE >= dateadd(day, datediff(day, 0, getdate()), @days)

I thought a positive DATEDIFF value meant that the first date parameter is older than the second date parameter.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 02:58:29

[code]SELECT *
FROM Jobs
WHERE End_Date >= DATEADD(DAY, DATEDIFF(DAY, 0, getdate()), 0)
AND End_Date < DATEADD(DAY, DATEDIFF(DAY, -1, getdate()), @Days)[/code]

N 56°04'39.26"
E 12°55'05.63"

titanotam
Starting Member

6 Posts

Posted - 2009-07-10 : 03:10:17

It's works
here is my code:
SELECT *
FROM Jobs
where datediff(day, getdate(),A.END_DT ) <= @DAYS
AND datediff(day, getdate(), A.END_DT) >= 0
Thanks all

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 03:21:20

You didn't pay any attention to what was written about performance, did you?

Your code can be simplified as
SELECT * FROM Jobs where datediff(day, getdate(),A.END_DT ) BETWEEN 0 AND @DAYS
but still suffer from a performance point of view.

Try my suggestion made 07/10/2009 : 02:58:29 and tell us what you think.

N 56°04'39.26"
E 12°55'05.63"

titanotam
Starting Member

6 Posts

Posted - 2009-07-10 : 03:36:27

Ok, it's more simple, Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources