getdate() as sp_executesql parameter

Dev Shed Forums Sponsor:

November 2nd, 2004, 06:59 AM

fundo

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

getdate() as sp_executesql parameter

I am working on somthing like this:

DECLARE @toWeekday nvarchar(300)
SET @toWeekday =N'SELECT CASE datepart(weekday,@theDay)
WHEN 1 THEN ''Sunday''
WHEN 2 THEN ''Monday''
WHEN 3 THEN ''Tuesday''
WHEN 4 THEN ''Wednesday''
WHEN 5 THEN ''Thursday''
WHEN 6 THEN ''Friday''
WHEN 7 THEN ''Saturday''
END'
EXEC sp_executesql @toWeekday,N'@theDay datetime','getdate()'

with the error message "Error converting data type varchar to datetime." Anyone has an idea? thanks

November 2nd, 2004, 08:02 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

Why making things complicated?

Code:

DECLARE @toWeekday varchar(10)
SET @toWeekday = datename(dw,getdate())

What's wrong with the above?

Last edited by swampBoogie : November 2nd, 2004 at 08:04 AM.

November 2nd, 2004, 08:16 AM

fundo

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

In fact I need german output weekday strings because I am writing code for german users. I just changed the output string to english before I posted the text ...

November 2nd, 2004, 08:58 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

Code:

DECLARE @toWeekday varchar(10)
SET @toWeekday = CASE datepart(weekday,getdate())
WHEN 1 THEN 'Sontag'
WHEN 2 THEN 'Montag'
WHEN 3 THEN 'Dienstag'
WHEN 4 THEN 'Mitwoch'
WHEN 5 THEN 'Donnerstag'
WHEN 6 THEN 'Freitag'
WHEN 7 THEN 'Samstag'
END

November 2nd, 2004, 09:21 AM

fundo

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

I also tried this and it does work

. The reason why I need to input the datetime value as parameter is the following:

the "EXEC sp_executesql @toWeekday,..." statement is intended to be called several times with different values like "dateadd(Day,-7,getdate())" instead of getdate(), in order to generate different weekday names, which should then be used as the column names of (some kind of reporting-)view. The time when this reporting view is called should be kept flexible, and the reporting period the last 7 days.

November 2nd, 2004, 09:33 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,912

Time spent in forums: 1 Month 4 Weeks 12 h 17 m 55 sec
Reputation Power: 1018

Mittwoch, two Ts

(i'm german, as you can guess by my lederhosen photo)

__________________
r937.com | rudy.ca

November 2nd, 2004, 10:23 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

I still don't see any need for using dynamic SQL

Code:

DECLARE @toWeekday varchar(10)
declare @d
set @d = dateadd(d,7,getdate())

SET @toWeekday = CASE datepart(weekday,@d)
WHEN 1 THEN 'Sontag'
WHEN 2 THEN 'Montag'
WHEN 3 THEN 'Dienstag'
WHEN 4 THEN 'Mittwoch'
WHEN 5 THEN 'Donnerstag'
WHEN 6 THEN 'Freitag'
WHEN 7 THEN 'Samstag'
END

or you could create a function for further flexibility

Code:

create function germanWeekday(@d datetime)
returns varchar(10)
as 
begin 
     return CASE datepart(weekday,@d)
WHEN 1 THEN 'Sontag'
WHEN 2 THEN 'Montag'
WHEN 3 THEN 'Dienstag'
WHEN 4 THEN 'Mittwoch'
WHEN 5 THEN 'Donnerstag'
WHEN 6 THEN 'Freitag'
WHEN 7 THEN 'Samstag'
END
end

November 3rd, 2004, 10:19 AM

fundo

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

I think these are the answers to my problem!

But what's the bad of dynamic sql, if you suggest that one should avoid using it?

p.s. Sonntag has 2 Ns

November 3rd, 2004, 11:13 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,912

Time spent in forums: 1 Month 4 Weeks 12 h 17 m 55 sec
Reputation Power: 1018

Quote:

Originally Posted by fundo

p.s. Sonntag has 2 Ns

doh!!! how could i have missed that?!!

#10

November 3rd, 2004, 01:54 PM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

Drawbacks of dynamic sql:

It requires more resources as it needs to be compiled at each invocation whereas a function is compiled once.

The code becomes more complex and thus harder to maintain.

Any privilege used in a dynamic query must be held by all users using the routine, otherwise it is sufficient that the creator of the routine holds the privilege.

#11

November 4th, 2004, 07:27 AM

fundo

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Thank you, swampBoogie.
Now I created the function toWeekday(@d datetime) and am having difficulty in generating the column name of my View with it:

Code:

CREATE VIEW JobsWeeklyView (cDM_JobDB.dbo.toWeekday(dateadd(Day,-7,getdate())))
as select 2

because query analyzer just won't accept that!

#12

November 4th, 2004, 10:48 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

I don't understand what you are trying to do with a view.

The syntax for a view definiton is

Code:

create view x <(columnNames)>
as
selectQuery

Views don't take parameters.

#13

November 8th, 2004, 03:35 AM

fundo

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

As a test here I am trying to create a view with one column.
The column name should be the weekday name 7 days ago, and the value 2 is only a
temporary place holder, so that the statement won't have a syntax problem.

The point is, what is wrong in my column name definition since function call is allowed according to

http://msdn.microsoft.com/library/d...reate2_30hj.asp

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: