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
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