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

Trying to convert varchar to datetime Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 1:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 1,059, Visits: 5,760
savycara (7/24/2013)
Actually 2011 is year and 4 is month which shoud be represented as 04 and date is 01 (which is to be added)

I need this column data to be in style 101


Your first post states that you want output as datetime. If you want your output as style 101, it would have to be varchar or char. Which do you want? Here's datetime:
SELECT *
FROM (SELECT strDate = '20114' UNION ALL SELECT '201112') d
CROSS APPLY (SELECT strDate2 = STUFF(strDate,5,0,CASE LEN(strDate) WHEN 5 THEN '0' ELSE '' END)+'01') x
CROSS APPLY (SELECT dtDate = CONVERT(DATETIME,x.strDate2,112)) y
CROSS APPLY (SELECT Result = CONVERT(DATETIME,STUFF(strDate,5,0,CASE LEN(strDate) WHEN 5 THEN '0' ELSE '' END)+'01',112)) z




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1477377
Posted Friday, July 26, 2013 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:36 PM
Points: 14, Visits: 13
@kapil

Thank you
Post #1478092
Posted Friday, July 26, 2013 8:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
This is a duplicate thread. Let's shift over to the other thread where there are more posts. No more posts on this thread, please. It's just dividing up the answers and making things harder to follow. Thanks folks.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478260
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse