Cast / convert date issue

Transact-SQL
1

FORMAT OF JOINING_DATE is 2013-01-01

SELECT FIRST_NAME , DATEPART(YEAR,JOINING_DATE) AS YEA , CONVERT (VARCHAR,DATEPART(MONTH,JOINING_DATE)) AS MON, DATEPART(DAY,JOINING_DATE) FROM employee

after execution of above sql code
i expected mon column as jan, feb but am seeing 1,2 respectively

please correct the code.
if any modification for the expected output pls let me know.

fIRST_NAME YEA MON DA
John 2013 1 1
Michael 2013 1 1
Roy 2013 2 13
Tom 2013 2 1
Jerry 2013 2 1
Philip 2013 1 1
TestName1 2013 1 1
TestName2 2013 2 1

2

Replace the above with this:

LEFT(DATENAME(MONTH,JOINING_DATE),3) AS MON

3

As a side note, always give size for VARCHAR. It may default to 1 instead of 50.

2 Likes
4

So assuming that JOINING_DATE is in String/Varchar format? I'm not sure I am comfortable with

because "2013-01-01" is UNambiguous for a DATE datatype, but AMbiguous for a Date/TIME (you need "20130101" [no punctuation] for an UNambiguous date/time string)

I can't actually create an example that will get it to fail (unless I change the Year to 2-digits though)

SET LANGUAGE US_English
GO
SET DATEFORMAT MDY
GO
SELECT DATEPART(YEAR, '2012-01-02'), DATEPART(MONTH, '2012-01-02'), DATEPART(DAY, '2012-01-02')
GO
SELECT DATEPART(YEAR, '12-01-02'), DATEPART(MONTH, '12-01-02'), DATEPART(DAY, '12-01-02')
GO

gives

Changed language setting to us_english.
                                    
----------- ----------- ----------- 
2012        1           2

----------- ----------- ----------- 
2002        12          1