How to convert datetime format in sqlserver [Resolved]

Posted by Naseer under Sql Server on 2/11/2014 | Points: 10 | Views : 3419 | Status : [Member] | Replies : 7
Hi,

i am trying to convert datetime '12/01/2013 AM 12:00:00' to jan-2013

how can i do in sqlserver ,please do the favour




Responses

Posted by: Bandi on: 2/11/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
--Sample code
Assumed that given date is of VARCHAR type....
DECLARE @date VARCHAR(40) = '12/01/2013 AM 12:00:00' -- to jan-2013 
SELECT LEFT(DATENAME(MONTH, CAST(SUBSTRING(@date, 1, CHARINDEX(' ', @date)-1) AS DATETIME)), 3) + ' ' + DATENAME(YEAR, CAST(SUBSTRING(@date, 1, CHARINDEX(' ', @date)-1) AS DATETIME)) AS [Mon YYYY]


If given date is DATETIME, then

DECLARE @date DATETIME = '12/01/2013 12:00:00 AM' -- to jan-2013
SELECT LEFT(DATENAME(MONTH, @date), 3) + ' ' + DATENAME(YEAR, @date) 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: Learningtorise on: 2/11/2014 [Member] Starter | Points: 25

Up
0
Down
Guideline:

http://www.sql-server-helper.com/tips/date-formats.aspx

http://hashtagakash.wordpress.com/

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

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

Up
0
Down
Thanq bandi

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

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

Up
0
Down
You are Welcome [:)]

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: Snaveen on: 2/13/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

Use below sample code to achieve your goal.

EX:

select convert(varchar(15), convert (varchar(3), datename(m,getdate())) +' - '+ convert (varchar(4), year(getdate()))) As Dat

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

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

Up
0
Down
Hi friends i did it with your help using

SELECT LEFT(DATENAME(MONTH ,CONVERT(VARCHAR(10), '5/03/2015 AM 12:00:00', 112)),3)
+'-'+ DATENAME(YEAR,CONVERT(VARCHAR(10), '1/02/2014 AM 12:00:00', 112))


its working fine in my local system but it inserting wrong mounth in database

ex:expected output-May-2015

Getting output-Jan-2015

Kindly help me.

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

Posted by: Mohammedsabeer on: 3/5/2014 [Member] Starter | Points: 25

Up
0
Down
Haai Naseer...

Actualy you gave the '5/03/2015 AM 12:00:00' as date to pick the Month. This is wrong. :)

Please update your code as below.

SELECT LEFT(DATENAME(MONTH ,CONVERT(VARCHAR(10), '1/02/2014 AM 12:00:00', 112)),3)
+'-'+ DATENAME(YEAR,CONVERT(VARCHAR(10), '1/02/2014 AM 12:00:00', 112))

Hope this will help...

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

Login to post response