How convert dd-Mon-yy to yyyymmdd

Question

Post reply

How convert dd-Mon-yy to yyyymmdd

Lisset

SSCommitted

Points: 1858
More actions
March 23, 2011 at 7:17 pm

#237344

Hi everybody I want to convert data like '27-Ene-11' to format yyyy-mm-dd, for this I'm using the script:
select convert(char(8),cast('27 Ene 11' as smalldatetime),112)
and show the error :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
Please I need your help!
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

this might help...

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1aa3b0df-8d9e-489d-90f2-d441bb241b8e/

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Lisset SSCommitted Points: 1858 More actions · Answer 2

Lisset

SSCommitted

Points: 1858

March 24, 2011 at 9:43 am

#1303066

Thanks! I dont have the error!

UMG Developer SSChampion Points: 13482 More actions · Answer 3

Lisset (3/23/2011)
Hi everybody I want to convert data like '27-Ene-11' to format yyyy-mm-dd, for this I'm using the script:
select convert(char(8),cast('27 Ene 11' as smalldatetime),112)
and show the error :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

You have a few problems:

* your CHAR(8) is too small for the 10 character string you want.

* Convert format 112 doesn't match the format you are asking for. (Use 120)

* It is best to use CONVERT to convert your date/time string to a datetime value not CAST.

* Not sure if this is an issue, but your language would need to be Spanish to get the convert to work.

Try this:

SET LANGUAGE SPANISH;

SELECT CONVERT(CHAR(10), CONVERT(SMALLDATETIME, '27 Ene 11', 13), 120);

UMG Developer SSChampion Points: 13482 More actions · Answer 4

I just noticed that the formats, both source and final, you list in the subject don't match what you have in your code, so I'm not sure which is supposed to be used.