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

Posted by Naseer under Sql Server on 2/27/2014 | Points: 10 | Views : 1067 | Status : [Member] | Replies : 7
hi friends,

help me to convert from '10/1/2014 12:00:00 AM' to Oct-2014 in sqlserver

Thanks in advance.




Responses

Posted by: kgovindarao523-21772 on: 2/27/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
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

Posted by: Naseer on: 2/27/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Murugavelmsc on: 2/27/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Naseer on: 2/27/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Bandi on: 2/27/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 3/2/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: kgovindarao523-21772 on: 3/3/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
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

Login to post response