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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 17, Visits: 78
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 3,374, Visits: 7,300
What about using the code 120?
CONVERT( datetime, @myDate, 120)




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 17, Visits: 78
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