How to convert datetime format in sqlserver [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

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

Thanq bandi

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

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

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

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

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