Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

converting date time from string Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 8:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 7:18 PM
Points: 26, Visits: 50
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!
Post #1536793
Posted Friday, January 31, 2014 8:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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 2008, MVP
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

Post #1536796
Posted Friday, January 31, 2014 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 12,744, Visits: 31,069
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1536797
Posted Friday, January 31, 2014 8:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 7:18 PM
Points: 26, Visits: 50
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



Post #1536799
Posted Friday, January 31, 2014 8:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 7:18 PM
Points: 26, Visits: 50
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
Post #1536801
Posted Friday, January 31, 2014 11:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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 2008, MVP
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

Post #1536916
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse