Select Query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Select Query

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2012-03-22 : 11:59:59

I am doing a select using the below where TotalDays is a field in the table.

Select * from table1
GETDATE() >= ticket_date + (31 - TotalDays)

I'm not sure if what I am doing is correct.

Does anyone know if this would look like the correct syntax?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-22 : 12:02:35

WHERE is missing

No, you're never too old to Yak'n'Roll if you're too young to die.

macca
Posting Yak Master

146 Posts

Posted - 2012-03-22 : 12:07:15

That was a typo ... other than that does it look ok?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-22 : 12:21:56

You want to worry about your database using an index or not.

Your current construct is nonsargable (not searc arguable..meaning a scan)

There are a bunch of date manipulations

But I found another one here

http://stackoverflow.com/questions/353014/convert-sql-server-datetime-fields-to-compare-date-parts-only-with-indexed-look


SELECT * 
  FROM table1
 WHERE ticket_date <= CAST(FLOOR(CAST( DATEADD(dd,31,GETDATE()) AS float )) AS datetime)

And I guess if you are using SQL Server 2008 R2


 SELECT * 
  FROM table1
 WHERE ticket_date <= DATEADD(dd,31,CONVERT(date,GETDATE()))

And there's another one used here at SQL Team a lot, I just can't find it

Good Luck

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

macca
Posting Yak Master

146 Posts

Posted - 2012-03-22 : 12:26:55

I'm not sure if that's what I'm looking for.
I'm doing a select where Today's date is greater than or equal to ticket_date plus 31 days minus totaldays field value i.e.

Select * from table1
Where GETDATE() >= ticket_date + (31 - TotalDays)

you don't make any reference to TotalDays in your examples.

Maybe i'm wrong??

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-22 : 12:39:02

quote:
Originally posted by X002548

You want to worry about your database using an index or not.

Your current construct is nonsargable (not searc arguable..meaning a scan)

There are a bunch of date manipulations

But I found another one here

http://stackoverflow.com/questions/353014/convert-sql-server-datetime-fields-to-compare-date-parts-only-with-indexed-look

SELECT *
FROM table1
WHERE ticket_date <= CAST(FLOOR(CAST( DATEADD(dd,31,GETDATE()) AS float )) AS datetime)

And I guess if you are using SQL Server 2008 R2

SELECT *
FROM table1
WHERE ticket_date <= DATEADD(dd,31-TotalDays,CONVERT(date,GETDATE()))

And there's another one used here at SQL Team a lot, I just can't find it

Good Luck

Brett

8-)

No, you're never too old to Yak'n'Roll if you're too young to die.

macca
Posting Yak Master

146 Posts

Posted - 2012-03-26 : 06:30:10

Okay guys Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources