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 ««12

Error in Drop Create stored procedure. Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 11:43 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 12:59 AM
Points: 38, Visits: 145
Am I correct to assume that @myDate is our starting point?
And that CONVERT only changes the display of the date string according to the added parameter (111 or 105).
Then the calculations should give the same results...
J.

How do you "tell" SQL Server that @myDate is in this format: yyyy-mm-dd?


Declare @myDate varchar(50)
Set @myDate = '2014-04-08'

SELECT CONVERT( char(20), CONVERT( datetime, @myDate))

select (convert(datetime, @myDate,111))+22
select convert(char(20), (convert(datetime, @myDate,111))+22)

select (convert(datetime, @myDate,105))+22
select convert(char(20), (convert(datetime, @myDate,105))+22)

This gives identical results:

Declare @myDate varchar(50)
Set @myDate = '2014/dec/10' --yyyy-mm-dd

SELECT CONVERT( char(20), CONVERT( datetime, @myDate))

select (convert(datetime, @myDate,111))+22
select convert(char(20), (convert(datetime, @myDate,111))+22)

select (convert(datetime, @myDate,105))+22
select convert(char(20), (convert(datetime, @myDate,105))+22)

Post #1566074
Posted Tuesday, April 29, 2014 12:09 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 4,043, Visits: 9,190
What about using the code 120?
CONVERT( datetime, @myDate, 120)




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1566082
Posted Tuesday, April 29, 2014 12:23 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 12:59 AM
Points: 38, Visits: 145
I might strip out the "-" to prevent issues.
See this post, last message.

Here I get consistant results:
Declare @myDate varchar(50)
Set @myDate = '20141210' --yyyymmdd


SELECT CONVERT( char(20), CONVERT( datetime, @myDate))

select (convert(datetime, @myDate,111))+22
select convert(char(20), (convert(datetime, @myDate,111))+22)

select (convert(datetime, @myDate,105))+22
select convert(char(20), (convert(datetime, @myDate,105))+22)

select (convert(datetime, @myDate,120))+22
select convert(char(20), (convert(datetime, @myDate,120))+22)


Post #1566092
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse