January 31, 2014 at 8:06 am
I am practicing my first stored proc and have written the following:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE GetDay @Day DATE
AS
BEGIN
SET NOCOUNT ON;
SELECT @Day = cast(datename(dw, getdate()) as date)
END
GO
DECLARE @day date;
EXEC getday @day
through reading Microsoft book online I read that daetname function returns nvarchar so I tried to cast my result as date.
Where am I missing the data conversion?
Thanks!
January 31, 2014 at 8:08 am
What should that procedure return? If I pass it current date (2014/01/31), what do you expect to get back?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2014 at 8:10 am
I think you simply want SET @Day = cast( getdate() as date)
you have a DATENAME function that would reurn 'FRIDAY", which you tehn try to cast as a date, and that's the error you are getting.
breaking down the three steps of your function might help you visualize
select
getdate() as date, --2014-01-31 10:08:44.047
datename(dw, getdate()), --Friday
cast( getdate() as date) --2014-01-31
maybe you just want to have the procedure return Friday, for example?
THEN the proc should be like this?
create PROCEDURE GetDay @Day DATE
AS
BEGIN
SET NOCOUNT ON;
SELECT datename(dw, @Day)
END
GO
Lowell
January 31, 2014 at 8:16 am
I am trying to pass it a date and have it return day of week. But the first thing I wanted to try was to just create a SP that would return me the day of the week.
Ex. Thursday
January 31, 2014 at 8:19 am
Lowell (1/31/2014)
I think you simply want SET @Day = cast( getdate() as date)you have a DATENAME function that would reurn 'FRIDAY", which you tehn try to cast as a date, and that's the error you are getting.
breaking down the three steps of your function might help you visualize
select
getdate() as date, --2014-01-31 10:08:44.047
datename(dw, getdate()), --Friday
cast( getdate() as date) --2014-01-31
maybe you just want to have the procedure return Friday, for example?
THEN the proc should be like this?
create PROCEDURE GetDay @Day DATE
AS
BEGIN
SET NOCOUNT ON;
SELECT datename(dw, @Day)
END
GO
l
This is perfect!! Thanks
January 31, 2014 at 11:26 am
caippers (1/31/2014)
I am trying to pass it a date and have it return day of week. But the first thing I wanted to try was to just create a SP that would return me the day of the week.
In that case you wouldn't want to case it back to date, as you want a string containing the name, not a date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply