CODEWITHSUNDEEP
sql-serverdate
Rastaw
Rastaw

Reputation: 21

How to convert DATETIME result into DATE on SQL Server

Here is the logic which isn't working as it returns a DATETIME result. Any help would be much appreciated. I've tried to change the data type to DATETIME2 but not working.

here is the code :

DECLARE @CurrentDate DATE = '0001-01-01' 
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter, 
       DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter, 
       CONVERT(VARCHAR(10), DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, (6- 
       DATEPART(DAY, @CurrentDate)), @CurrentDate)),0),120) AS FirstMondayOftheMonth

Upvotes: 1

Views: 192

Answers (2)

A Hettinger
A Hettinger

Reputation: 458

Depends on the version, but SELECT CONVERT(date, getdate()) is probably the best approach (assuming you're on 2008 or later, which, at this point, you should be).

Obviously replace getdate() with whatever you need it to convert.

Upvotes: 1

sandeep rawat
sandeep rawat

Reputation: 4957

Date data type can handle 1582-10-15 to 9999-12-31 and here you are trying '0001-01-01'.

Details read

Upvotes: 1

Related Questions