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
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:41 PM
Points: 1,090, Visits: 8,123
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:
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 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


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

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



Group: General Forum Members
Last Login: Today @ 2:21 PM
Points: 38,597, Visits: 35,607
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

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