converting date time from string

Question

converting date time from string

caippers

Mr or Mrs. 500

Points: 522
More actions
January 31, 2014 at 8:06 am

#289010

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!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

caippers Mr or Mrs. 500 Points: 522 More actions · Answer 3

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

caippers Mr or Mrs. 500 Points: 522 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass