Questions tagged [dateadd]

dateadd is a T-SQL function that returns a datetime with the specified number interval added to a specified datepart of that datetime.

dateadd(datepart,number_of_units,date_time_value) is a T-SQL function available in MS SQL Server and the SAP/Sybase family of database products that can add and subtract from datetime values. Subtraction is accomplished by using a negative number_of_units value.

datepart is usually a two letter code which specifies which date or time segment is being added number of units is a signed int value that specifies how much to add to the datepart date_time_value is the datetime that is being manipulated.

SQL Server Syntax

SAP/Sybase Syntax

498 questions
6
votes
3 answers

How to subtract one month from a Date Column

I know about Dateadd and datediff, but I cannot find any information how to use these functions on an actual date column rather than something like today's date with SQL Server. Say I have the following Column Dated 06/30/2015 07/31/2015 Now I want…
Jay C
  • 842
  • 6
  • 17
  • 37
6
votes
1 answer

Don't understand why DATEADD is not incrementing datatime

Migrating data from Access into SQL Server. SQL Server table defines inst_id, cons_code, and eff_date_time column as primary key. The eff_date_time data coming from access is not unique so I was attempting to increment the seconds field by one…
5lb Bass
  • 539
  • 8
  • 15
5
votes
3 answers

Does SQL Server optimize DATEADD calculation in select query?

I have a query like this on Sql Server 2008: DECLARE @START_DATE DATETIME SET @START_DATE = GETDATE() SELECT * FROM MY_TABLE WHERE TRANSACTION_DATE_TIME > DATEADD(MINUTE, -1440, @START_DATE) In the select query that you see above, does SqlServer…
Ahmet Altun
  • 3,910
  • 9
  • 39
  • 64
5
votes
4 answers

Using Parameters in DATEADD function of a Query

I am trying to us the DateAdd function of SQL in my Query. The problem is when I use a parameter to set the second arguement, the number argument I get an error which will say something like this: Failed to convert parameter value from a Decimal…
Damien
  • 13,927
  • 14
  • 55
  • 88
5
votes
4 answers

INSERT INTO SQL DATEADD Yesterday

I want to use PHP and MySQL to INSERT the the day of Yesterday. So my idea was: INTO chartValues SET timestamp='1353369600', `datetime`=DATEADD(d,-1,GETDATE()) But its not working: 1064 - You have an error in your SQL syntax; check the manual…
number5
  • 93
  • 2
  • 2
  • 9
5
votes
3 answers

Using sql DATEADD function in java

When I run queries using DATEADD it seems that the database does not recognize this function. also when I just run select DATEADD(Month, -3, GETDATE()) I'm getting: Error code -1, SQL state 42X01: Syntax error: Encountered "" at line 1, column…
Onca
  • 1,113
  • 3
  • 17
  • 31
4
votes
6 answers

How to add an hour in timestamp in sql server (without Declare)

I want to add an hour in the parameter TimeStamp, but not with declare parameter i.e DECLARE @datetime2 datetime2 = '2019-03-01T09:25:21.1+01:00' SELECT DATEADD(hour,1,@datetime) I have a column name TimeStamp in a table and i…
chrysa22
  • 69
  • 1
  • 1
  • 6
4
votes
3 answers

How do I convert a value after using DATEADD with it

I have a little query that strips the date from the datetime field but when I try to convert it from GMT to CST it readds the date. Is there a better way to do this? Location table: arrival 4-6-2018 12:35:43 SELECT arrival FROM( SELECT…
IowaMatt
  • 57
  • 1
  • 3
  • 10
4
votes
2 answers

Convert specific BigInt to DateTime in T-SQL

I have bigInt: 635307578922100000 which I need to convert to DateTime. I've tried few ways to do this: SELECT DATEADD(S, CONVERT(bigint,635307578922100000) / 1000, CONVERT(DATETIME, '1-1-1970 00:00:00')) and: SELECT DATEADD(ms,…
Tali B.
  • 125
  • 1
  • 11
4
votes
2 answers

When I enter a dateadd or datediff code i get this error all the time "ORA-00904 "DATEADD" INVALID IDENTIFIER."

I have a university project and I have a patient table with admission and discharge date attributes. I need to delete records that are older than 7 years, I used the following code : delete from patient where dis_date >=…
Frank
  • 59
  • 1
  • 1
  • 4
3
votes
2 answers

DATEADD - How do i make comparisons

I am trying to check if the second attempt(u can imagine anything like password attempt, download attempt etc) is made within a time limit(@window) starting from attempt1 time(@start). Why does this report a Syntax error when other datetime…
Deeptechtons
  • 10,945
  • 27
  • 96
  • 178
3
votes
1 answer

TSQL - Add missing dates

I have a query that results in the table shown below: select * from (select [iKey], [StartDate], [FirstFCDate] from (SELECT [iKey], min([Date]) as [FirstFCDate] from dbo.factFC group by [iKey]) as gp left outer join (select [StartDate], [Key] from…
SUMguy
  • 1,505
  • 4
  • 31
  • 61
3
votes
3 answers

DATEADD issue when negative number is used with MONTH datepart

From the DATEADD documentation: If datepart is month and the date month has more days than the return month and the date day does not exist in the return month, the last day of the return month is returned. For example, September has 30 days;…
gotqn
  • 42,737
  • 46
  • 157
  • 243
3
votes
2 answers

date_add() changing 2 variables in PHP rather than 1

Here is an example of the code I used: "; echo…
RJE95
  • 45
  • 4
3
votes
2 answers

how to using dateadd in sqlalchemy with filter?

The sql expression : select * from order where status=0 and adddate(created_time, interval 1 day)>now(); python code: from sqlalchemy.sql.expression import func, text from datetime import datetime closed_orders =…
dived
  • 31
  • 1
  • 3
1
2
3
33 34