CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005sql-server-2000
user1254579
user1254579

Reputation: 4009

How to get correct date part from variable sql

There is a need to get the year,month and day part from a date variable.when fired the below query ,it gives day as 8 and month as 4

declare @date1 datetime
set @date1='04/08/2014'
        ------'dd/mm/yyyy' 

--select @date1,year(@date1) year1,month(convert(@date1,datetime)) month1,day(@date1) day1
select   YEAR(CONVERT(DATETIME,@date1)) YEAR1,
            MONTH(CONVERT(DATETIME,@date1)) MOTNH1,
            DAY(CONVERT(DATETIME,@date1)) DAY1
FROM            Tab1

Upvotes: 0

Views: 444

Answers (1)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Check this for DateTime possible formats

declare @date1 datetime
set @date1=CONVERT(DATETIME,'04/08/2014',103)

SELECT  YEAR(@date1) YEAR1,
        MONTH(@date1) MOTNH1,
        DAY(@date1) DAY1

Make a note thar dd/MM/yyyy is different than dd/MM/yy. The first need 103 as the format switch and the latter needs 3.

Upvotes: 2

Related Questions