Dynamically created DATEDIFF function

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 2000 Forums

Transact-SQL (2000)

Dynamically created DATEDIFF function

Author

Topic

yaron-b
Starting Member

4 Posts

Posted - 2007-08-26 : 09:26:56

Hi!

Does anyone know if I can dynamically create a DATEDIFF function within a query.

Example:
[CODE]SELECT * FROM myTable
WHERE (DATEDIFF(intervalCode, lastExecution, GETDATE()) >= intervalNumber)[/CODE]

As you can see, I filled up all the arguments with data from columns. The problem is that the first argument (underlined above) doesn't have a data type. Normally it should be written as a kind of a "keyword", like this:

[CODE]SELECT * FROM myTable
WHERE (DATEDIFF(yy, lastExecution, GETDATE()) >= intervalNumber)[/CODE]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-26 : 09:59:32

[code]
SELECT * FROM myTable
WHERE case
when intervalCode = 'yy' then
(DATEDIFF(yy, lastExecution, GETDATE())
when intervalCode = 'd' then
(DATEDIFF(d, lastExecution, GETDATE())
when intervalCode = 'm' then
(DATEDIFF(m, lastExecution, GETDATE())
-- ...others
end >= intervalNumber)
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

gksharma
Starting Member

11 Posts

Posted - 2007-08-26 : 10:04:59

Intervalcode required to cast/convert to get datepart abbreviations for datediff.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-26 : 10:40:15

And DATEDIFF do not always show the result you would expect
http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.25"
N 56°04'39.16"

yaron-b
Starting Member

4 Posts

Posted - 2007-08-26 : 11:51:36

quote:

Originally posted by spirit1


SELECT * FROM myTable
WHERE  case 
          when intervalCode = 'yy' then 
                   (DATEDIFF(yy, lastExecution, GETDATE())
          when intervalCode = 'd' then 
                   (DATEDIFF(d, lastExecution, GETDATE())
          when intervalCode = 'm' then 
                   (DATEDIFF(m, lastExecution, GETDATE())
          -- ...others
       end >= intervalNumber)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Thanks spirit1, but it didn't work for me.
In the Query Analyzer, I get this error message:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'when'.

Do you know what's wrong?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-26 : 11:59:17

there were a few extra brackets in there.


declare @intervalCode varchar(2)
select @intervalCode = 'yy'
SELECT * FROM myTable
WHERE  case 
          when @intervalCode = 'yy' then 
                   DATEDIFF(yy, lastExecution, GETDATE())
          when @intervalCode = 'd' then 
                   DATEDIFF(d, lastExecution, GETDATE())
          when @intervalCode = 'm' then 
				   DATEDIFF(m, lastExecution, GETDATE())
          -- ...others
       end >= intervalNumber

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources