How to Add days to a date column along with time in SQL
When you use + 1
, the timestamp
is converted to a date
. That is why you are only seeing the date (the default representation of a date
does not include the time component although it is there).
Instead, use interval
:
select read_time, read_time + interval '1' day
from meter_read_alert;
PS. I learned something from this. Interesting question.
Adding days to a date in a table - SQL
It is simple, try the below SQL
Select TO_CHAR(DateQualified + 15,'Month fmDD, YYYY') as "Reminder Date"
FROM Qualified;
Oracle query- How subtract or add days in the given date
If I got it right, you need to change the upper date of "BETWEEN" clause.
In this case that clause might look like this
SELECT *
From HDB.MDTA E
WHERE E.MODIFIED_DAY_KEY BETWEEN TO_CHAR(${pEND_DATE}, 'YYYYMMDD') AND TO_CHAR(${pEND_DATE} + 123, 'YYYYMMDD');
The "123" is the number of days that will get added to the pEND_DATE parameter
Oracle - Add days/months to a TIMESTAMP WITH TIME ZONE field
When you do DATED = DATED + 2
, then DATED is implicitly converted to a DATE
value (which does not have any time zone information) and then converted back to TIMESTAMP WITH TIME ZONE
value using your current session time zone settings.
Try
UPDATE MY_TABLE SET DATED = DATED + INTERVAL '2' DAY WHERE ID = 1165;
or
UPDATE MY_TABLE SET DATED = DATED + 2 * INTERVAL '1' DAY WHERE ID = 1165;
or
UPDATE MY_TABLE SET DATED = DATED + NUMTODSINTERVAL(2, 'day') WHERE ID = 1165;
In order to add 2 months you can use
UPDATE MY_TABLE SET DATED = DATED + INTERVAL '2' MONTH WHERE ID = 1165;
resp. NUMTOYMINTERVAL(2, 'MONTH')
, for example.
PL/SQL function to add +7 days to SYSDATE
You have to format the result to your specifications, as in
--create the function
CREATE OR REPLACE FUNCTION get_date(n IN NUMBER)
RETURN DATE AS
BEGIN
RETURN SYSDATE + n;
END get_date;
--call the function
SELECT TO_CHAR(get_date(7), 'DD.MM.YYYY HH24:MI')
FROM dual;
Or your new requirement of no formatting outside the function
--create the function
CREATE OR REPLACE FUNCTION get_date(n IN NUMBER)
RETURN VARCHAR2 AS
BEGIN
RETURN TO_CHAR(SYSDATE + n,'DD.MM.YYYY HH24:MI');
END get_date;
--call the function
SELECT get_date(7)
FROM dual;
Related Topics
Determine What User Created Objects in SQL Server
"Column Not Allowed Here" Error in Insert Statement
Finding a Top Level Parent in SQL
How to Implement SQL Intersect and Minus Operations in Ms Access
Select Distinct from Multiple Fields Using SQL
How to Count in SQL All Fields with Null Values in One Record
Postgresql Where Count Condition
Postgresql - Dynamic Value as Table Name
How to Use a MySQL Database with an App Engine Application
Is There a Postgres Closest Operator
How to Automatically Generate Unique Id in SQL Like Uid12345678
How to Check If a Column Exists Before Adding It to an Existing Table in Pl/Sql
How to Create an Oracle Sequence Starting with Max Value from a Table
Function with SQL Query Has No Destination for Result Data
Difference Between "On .. And" and "On .. Where" in SQL Left Join