Sql: Subtracting 1 Day from a Timestamp Date

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: Subtracting 1 day from a timestamp date

Use the INTERVAL type to it. E.g:

--yesterday
SELECT NOW() - INTERVAL '1 DAY';

--Unrelated: PostgreSQL also supports some interesting shortcuts:
SELECT 
    'yesterday'::TIMESTAMP, 
    'tomorrow'::TIMESTAMP, 
    'allballs'::TIME AS aka_midnight;

You can do the following then:

SELECT 
    org_id,
    count(accounts) AS COUNT,
    ((date_at) - INTERVAL '1 DAY') AS dateat
FROM 
    sourcetable
WHERE 
    date_at <= now() - INTERVAL '130 DAYS'
GROUP BY 
    org_id,
    dateat;

TIPS

Tip 1

You can append multiple operands. E.g.: how to get last day of current month?

SELECT date_trunc('MONTH', CURRENT_DATE) + INTERVAL '1 MONTH - 1 DAY';

Tip 2

You can also create an interval using make_interval function, useful when you need to create it at runtime (not using literals):

SELECT make_interval(days => 10 + 2);
SELECT make_interval(days => 1, hours => 2);
SELECT make_interval(0, 1, 0, 5, 0, 0, 0.0);

More info:

Date/Time Functions and Operators

datatype-datetime (Especial values).

How to subtract 1 day from a timestamp in mysql

select (updt_dttm  - INTERVAL 1 DAY) as date from hstry where roll="X34" ;

How to subtract one day from current date then convert to string in Hive

The problem is the way you trying to subtract a day from date.I would suggest to subtract number of seconds in a day(86400) from unix timestamp in where clause-

CAST(TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()-86400))as date) 

Append random timestamps to a date field while subtracting days from todays date?

you can still use the above logic with some changes , you can choose SECOND or play with number param for DATEADD for tighter or wider range :

SELECT
   DATEADD(MILLISECOND ,CHECKSUM(NEWID()) ,GETDATE())    AS DUEDATE
FROM [dbo].[Table] as t

For example If you want to have controlled range for example for dates in 5 days range from A specific date:

you can declare @startdate whataver you want

DECLARE @dayrange int = 5

SELECT
   DATEADD(SECOND ,RAND(CHECKSUM(NEWID())) * 86400,CONVERT(datetime,CONVERT(varchar(8),DATEADD(DAY, - t.daysago ,GETDATE(),112))) 
   AS DUEDATE
FROM [dbo].[Table] as t

How to subtract 30 days from the current date using SQL Server

You can convert it to datetime, and then use DATEADD(DAY, -30, date).

See here.

edit

I suspect many people are finding this question because they want to substract from current date (as is the title of the question, but not what OP intended). The comment of munyul below answers that question more specifically. Since comments are considered ethereal (may be deleted at any given point), I'll repeat it here:

DATEADD(DAY, -30, GETDATE())

Subtracting Day from Postgresql EPOCH Column

You need to subtract an interval

to_timestamp(t.date_Created / 1000) - interval '5 days'

Integers can only be subtracted directly from a date value, not from a timestamp value.

Subtract 1 from current date/counting from yesterdays date

The above code works but it counts from today. I don't know how to make it start from yesterday

Use below

WHERE DATE(created_at) BETWEEN 
  DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) 
  AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

---

Related Topics