Datetime conversion

Question

Datetime conversion

monilps

SSCommitted

Points: 1686
More actions
January 29, 2016 at 9:54 am

#308106

Hi,
I need to convert varchar datatype "14-Apr" into datetime datatype "2014-04-01 00:00:00.000"
Thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Have a look at the CAST and CONVERT topic in Books Online. You may have to do a bit of manipulation on your string, for example adding a year on to it. Do you always want to return a date in 2014?

John

monilps SSCommitted Points: 1686 More actions · Answer 2

I looked at the CAST and CONVERT online but was not able to find the solution.

It would be always "YY-MON" (MON=First three letters of the month).

In meantime, I am still researching it.

Thanks.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

not sure but maybe?

SELECT TRY_CONVERT(datetime, '20'+'14-Apr') AS Result;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Maybe this:

DECLARE @date varchar(10) = '14-Apr'

SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6)

Luis C.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

Luis Cazares (1/29/2016)
Maybe this:
DECLARE @date varchar(10) = '14-Apr'
SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6)

Hi Luis......why not just use this instead??

SELECT CONVERT(datetime, '20'+@date)

??

also,I believe that TRY_CONVERT (2012+) will throw a NULL rather than an error

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

J Livingston SQL (1/29/2016)
Luis Cazares (1/29/2016)
Maybe this:
DECLARE @date varchar(10) = '14-Apr'
SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6)
Hi Luis......why not just use this instead??
SELECT CONVERT(datetime, '20'+@date)
??
also,I believe that TRY_CONVERT (2012+) will throw a NULL rather than an error

I'm still afraid of previous century. :hehe:

DECLARE @date varchar(10) = '99-Apr';

SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6),

CONVERT(datetime, '20'+@date) ;

Luis C.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

Luis Cazares (1/29/2016)
J Livingston SQL (1/29/2016)
Luis Cazares (1/29/2016)
Maybe this:
DECLARE @date varchar(10) = '14-Apr'
SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6)
Hi Luis......why not just use this instead??
SELECT CONVERT(datetime, '20'+@date)
??
also,I believe that TRY_CONVERT (2012+) will throw a NULL rather than an error
I'm still afraid of previous century. :hehe:
DECLARE @date varchar(10) = '99-Apr';
SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6),
CONVERT(datetime, '20'+@date) ;

fair enough...see your (very valid) point. but why use "CONVERT" instead of "TRY_CONVERT"? (2012 forum)

Thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

J Livingston SQL (1/29/2016)
fair enough...see your (very valid) point. but why use "CONVERT" instead of "TRY_CONVERT"? (2012 forum)
Thanks

Bad practice. I didn't see it was a 2012 forum and I'm used on getting restricted to 2008. Fortunately, that's changing. Also, the syntax is the same, so hopefully the OP will choose the best option (TRY_CONVERT).

Luis C.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101092 More actions · Answer 9

Since the final date format is "self delimiting", you don't have to add spaces in the result. But I would adjust the code to allow the day to be only a single digit, Edit:and to delimit the day by any nonnumeric char, not just '-':

SELECT CONVERT(datetime, '1' + RIGHT(date,3) + LEFT(date,CHARINDEX('-',date)-1), 6) --Edit

SELECT CONVERT(datetime, '1' + RIGHT(date,3) + LEFT(date,PATINDEX('%[^0-9]%',date)-1), 6) --Edit

FROM (

SELECT '99-Apr' AS date UNION ALL

SELECT '14-Aug' UNION ALL

SELECT '2-Sep' UNION ALL

SELECT '5Dec' --Edit

) AS test_data

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Luis Cazares (1/29/2016)
J Livingston SQL (1/29/2016)
fair enough...see your (very valid) point. but why use "CONVERT" instead of "TRY_CONVERT"? (2012 forum)
Thanks
Bad practice. I didn't see it was a 2012 forum and I'm used on getting restricted to 2008. Fortunately, that's changing. Also, the syntax is the same, so hopefully the OP will choose the best option (TRY_CONVERT).

The TRY_CONVERT should also handle an invalid month string without throwing an error. Since the column is probably a varchar, you can't be sure of what'll be in there.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 11

Ed Wagner (1/29/2016)
Luis Cazares (1/29/2016)
J Livingston SQL (1/29/2016)
fair enough...see your (very valid) point. but why use "CONVERT" instead of "TRY_CONVERT"? (2012 forum)
Thanks
Bad practice. I didn't see it was a 2012 forum and I'm used on getting restricted to 2008. Fortunately, that's changing. Also, the syntax is the same, so hopefully the OP will choose the best option (TRY_CONVERT).
The TRY_CONVERT should also handle an invalid month string without throwing an error. Since the column is probably a varchar, you can't be sure of what'll be in there.

In such a case, I'd likely want it to throw an error.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)