convertion from '10/1/2014 12:00:00 AM' to Oct-2014 in sql server

Hi,
try this Code

with testdata as
(
  select cast('10/1/2014 12:00:00 AM' as datetime) as d
)
SELECT DATENAME(MONTH,d)+'-'+DATENAME(year,d)

Thank you,
Govind

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

Thanq Govind,

but i raising a error

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

with testdata as
(
select cast('15/1/2014 12:00:00 AM' as datetime) as d
)
SELECT DATENAME(MONTH,d)+'-'+DATENAME(year,d)

its not acepting more than 12

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

DECLARE @InputDate AS varchar(100)
SET @InputDate = (select cast('10/01/2014 12:00:00 AM' as datetime))
select substring(convert(char(20), @InputDate, 100), 1, 3)+ '-' + convert(char(4), year(@InputDate))

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

Thanq Murugavelmsc,

getting error while giving more than 12

DECLARE @InputDate AS varchar(100)
SET @InputDate = (select cast('15/01/2014 12:00:00 AM' as datetime))
select substring(convert(char(20), @InputDate, 100), 1, 3)+ '-' + convert(char(4), year(@InputDate))

error:The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

Check

SET DATEFORMAT DMY
DECLARE @InputDate AS varchar(100) = '10/1/2014 12:00:00 AM'
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), cast( @InputDate as datetime), 106), 8), ' ', '-') AS [Mon-YYYY]

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

Mark This Response as Answer link if the above solution helped you

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

Hi Naseer,

You specified that query is not accepting more than 12.
Please note that if you want to give more 12, It should be PM, right?
for ex: if want to 13 hrs, it is 1 PM.

Thank you,
Govind

Naseer, if this helps please login to Mark As Answer. | Alert Moderator