SQL Server 2005 Using Dateadd to Add a Day to a Date

SQL Server 2005 - DATE ADD AND GETDATE DOESN'T WORK

GETDATE() returns the time component of the date. In SQL Server 2005, you can remove the time component by doing:

DATASCADENZA = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

You can add three days as:

DATASCADENZA = DATEADD(day, 3 + DATEDIFF(day, 0, GETDATE()), 0)

Starting in SQL Server 2008, you can just convert to the DATE data type:

DATASCADENZA = CAST(GETDATE() as DATE)

I much prefer this formulation to adding days to some mysterious "0" date.

SQL add one day to start date

With the DATEADD function:

CONCAT(FORMAT(DATEADD(dd,1,sc.StartDate),'dd/MM/yyy'), + ' - ' + FORMAT(sc.ENDDate,'dd/MM/yyy')) SIPDate

MSDN reference:

https://msdn.microsoft.com/en-us/library/ms186819.aspx

SQL Server 2005: arithmetic with dates

This will retrieve monday for the current week

Select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

and then you need to subtract 70 from the above day

SELECT Dateadd(DAY,-70,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))

Edit : Please go through the answer posted in SO

Monday is displayed as Current Week because DATEFIRST which indicates the 1st day of the week is set to monday .In order to set it to Sunday ,you need to change the setting to Sunday

 Set DATEFIRST 7

Else as suggested in the above SO link ,you need to change your code

 DECLARE @dt DATE = '1905-01-01';
 SELECT [start_of_week] = DATEADD(WEEK, DATEDIFF(WEEK, @dt, CURRENT_TIMESTAMP), @dt);

SQL convert DateTime to Date (SQL server 2005)

There is no Date datatype in SQL Server 2005, it was added in SQL Server 2008.

To go with your second alternative, you can truncate the datetime to midnight on the same day:

SELECT DATEADD(DAY, DATEDIFF(DAY,0, YourDateColumn), 0)
FROM   YourTable

Define a parameter for searching on days DATEADD / datediff - SQL server 2005

You still need to perform an operation. If you want to pass in -30 then you need to add, if you want to pass in 30 then you need to subtract.

If @dayparameter is -30:

dateadd(day, datediff(day, 0 ,getdate()) + @dayparameter, 0) and getdate())
-----------------------------------------^ this operator is important

If @dayparameter is 30:

dateadd(day, datediff(day, 0 ,getdate()) - @dayparameter, 0) and getdate())
-----------------------------------------^ this operator is still important

---

Related Topics