SQL Server 2005: How to Subtract 6 Month

SQL Server 2005: how to subtract 6 month

You can use DATEADD:

select DATEADD(month, -6, @d)

EDIT: if you need the number of days up to 6 months ago you can use DATEDIFF:

select DATEDIFF(day, @d, DATEADD(month, -6, @d))

Subtract minute from DateTime in SQL Server 2005

SELECT DATEADD(minute, -15, '2000-01-01 08:30:00'); 

The second value (-15 in this case) must be numeric (i.e. not a string like '00:15'). If you need to subtract hours and minutes I would recommend splitting the string on the : to get the hours and minutes and subtracting using something like

SELECT DATEADD(minute, -60 * @h - @m, '2000-01-01 08:30:00'); 

where @h is the hour part of your string and @m is the minute part of your string

EDIT:

Here is a better way:

SELECT CAST('2000-01-01 08:30:00' as datetime) - CAST('00:15' AS datetime)

subtracting month AND days with dateadd()

SELECT DATEADD(dd, -6, DATEADD(mm,-1, GETDATE()))

Subtract one day from datetime

Try this

SELECT DATEDIFF(DAY,  DATEADD(day, -1, '2013-03-13 00:00:00.000'), GETDATE())

OR

SELECT DATEDIFF(DAY,  DATEADD(day, -1, @CreatedDate), GETDATE())

SQL Subtract exactly a year

Based on your comment regarding hard coded year values, the use of

 DATEDIFF(year,BOOKED,GETDATE())

to get the number of years since the date you're after should hopefully lead you in the direction you are after.

You will probably end up with something like:

SELECT DATEADD(year, -DATEDIFF(year,BOOKED,GETDATE()), GETDATE())

---

Ok, it looks more like all you really want to do (I may be wrong, sorry if so) is to group the bookings by year.

Will the result of the following help achieve that?

SELECT SDESCR,DATEADD(YEAR, DATEDIFF(YEAR, 0, BOOKED),0), Sum(APRICE) as Total, Sum(PARTY) as PAX
FROM  DataWarehouse.dbo.B01Bookings AS B101Bookings
GROUP BY SDESCR,DATEADD(YEAR, DATEDIFF(YEAR, 0, BOOKED),0)

As I said, this is a guess as to your goal, not necessarily an answer to your question.

get todays date with last 6 months back

if you want the EXACT joining date ( /date_of_joining.... /Dt_Of_Join)
what about

select distinct employee.name from emp_mst where date_of_joining = DATEADD(month, -6, GETDATE()) 

or if you want the actual date returned in a different format:

 CONVERT(Date,DATEADD(month, -6, GETDATE()), 103)

which is applicable if you select this field

---

Related Topics