1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rastaw
  • 21
  • 3
  • 4
    Possible duplicate of [How to return only the Date from a SQL Server DateTime datatype](https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype) – Ken White Jul 10 '18 at 02:39

2 Answers2

1

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

Details read

sandeep rawat
  • 4,797
  • 1
  • 18
  • 36
  • thank you for your reply. I've just read the link you sent and found out that 1582-10-15 to 9999-12-31 is actually for Informatica. – Rastaw Jul 10 '18 at 03:45
1

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.

A Hettinger
  • 448
  • 2
  • 11
  • @A Hettinger Thank you for your reply. I'm using SQL Server 2017 and the problem arises when I use the DATEDIFF and DATEADD functions as it returns DATETIME result. – Rastaw Jul 10 '18 at 03:56
  • You should be able to wrap each of the DATEDIFF and DATEADD calls with the CONVERT. – A Hettinger Jul 11 '18 at 03:19