66

How do I in SQL Server 2005 use the DateAdd function to add a day to a date

Dana
  • 32,083
  • 17
  • 62
  • 73
test
  • 2,087
  • 5
  • 23
  • 22

6 Answers6

125

Use the following function:

DATEADD(type, value, date)

  • date is the date you want to manipulate

  • value is the integere value you want to add (or subtract if you provide a negative number)

  • type is one of:

  • yy, yyyy: year

  • qq, q: quarter

  • mm, m: month

  • dy, y: day of year

  • dd, d: day

  • wk, ww: week

  • dw, w: weekday

  • hh: hour

  • mi, n: minute

  • ss or s: second

  • ms: millisecond

  • mcs: microsecond

  • ns: nanosecond

SELECT DATEADD(dd, 1, GETDATE()) -- will return a current date + 1 day

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ilya Kochetov
  • 17,988
  • 6
  • 44
  • 60
23
DECLARE @MyDate datetime

-- ... set your datetime's initial value ...'

DATEADD(d, 1, @MyDate)
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
13

Try following code will Add one day to current date

select DateAdd(day, 1, GetDate())

And in the same way can use Year, Month, Hour, Second etc. instead of day in the same function

BJ Patel
  • 6,148
  • 11
  • 47
  • 81
9

The following query I have used in SQL Server 2008, it may be help you.

For add day

DATEADD(DAY,20,GETDATE())

*20 is the day quantity

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dilip Kr Singh
  • 1,418
  • 1
  • 18
  • 26
4
DECLARE @date DateTime
SET @date = GetDate()
SET @date = DateAdd(day, 1, @date)

SELECT @date
Dana
  • 32,083
  • 17
  • 62
  • 73
2
Select getdate() -- 2010-02-05 10:03:44.527

-- To get all date format
select CONVERT(VARCHAR(12),getdate(),100) +' '+ 'Date -100- MMM DD YYYY' -- Feb 5 2010
union
select CONVERT(VARCHAR(10),getdate(),101) +' '+ 'Date -101- MM/DDYYYY'
Union
select CONVERT(VARCHAR(10),getdate(),102) +' '+ 'Date -102- YYYY.MM.DD'
Union
select CONVERT(VARCHAR(10),getdate(),103) +' '+ 'Date -103- DD/MM/YYYY'
Union
select CONVERT(VARCHAR(10),getdate(),104) +' '+ 'Date -104- DD.MM.YYYY'
Union
select CONVERT(VARCHAR(10),getdate(),105) +' '+ 'Date -105- DD-MM-YYYY'
Union
select CONVERT(VARCHAR(11),getdate(),106) +' '+ 'Date -106- DD MMM YYYY' --ex: 03 Jan 2007
Union
select CONVERT(VARCHAR(12),getdate(),107) +' '+ 'Date -107- MMM DD,YYYY' --ex: Jan 03, 2007
union
select CONVERT(VARCHAR(12),getdate(),109) +' '+ 'Date -108- MMM DD YYYY' -- Feb 5 2010
union
select CONVERT(VARCHAR(12),getdate(),110) +' '+ 'Date -110- MM-DD-YYYY' --02-05-2010
union
select CONVERT(VARCHAR(10),getdate(),111) +' '+ 'Date -111- YYYY/MM/DD'
union
select CONVERT(VARCHAR(12),getdate(),112) +' '+ 'Date -112- YYYYMMDD' -- 20100205
union
select CONVERT(VARCHAR(12),getdate(),113) +' '+ 'Date -113- DD MMM YYYY' -- 05 Feb 2010


SELECT convert(varchar, getdate(), 20) -- 2010-02-05 10:25:14
SELECT convert(varchar, getdate(), 23) -- 2010-02-05
SELECT convert(varchar, getdate(), 24) -- 10:24:20
SELECT convert(varchar, getdate(), 25) -- 2010-02-05 10:24:34.913
SELECT convert(varchar, getdate(), 21) -- 2010-02-05 10:25:02.990


---==================================
-- To get the time
select CONVERT(VARCHAR(12),getdate(),108) +' '+ 'Date -108- HH:MM:SS' -- 10:05:53

select CONVERT(VARCHAR(12),getdate(),114) +' '+ 'Date -114- HH:MM:SS:MS' -- 10:09:46:223
SELECT convert(varchar, getdate(), 22) -- 02/05/10 10:23:11 AM
----=============================================
SELECT getdate()+1
SELECT month(getdate())+1
SELECT year(getdate())+1
David Basarab
  • 72,212
  • 42
  • 129
  • 156
Lakshmanan From INDIA
  • 21
  • 1