Oracle next_day function returns wrong date
sql,oracle,oracle11g,date-arithmetic
next_day(DATE '15-06-04', 'Friday') You just reinvented the Y2K bug. Year should always be YYYY format in complete 4 digits. Or, use RR format along with TO_DATE. There are certain rules for the RR format you need to take care. The ANSI date literal contains no time portion, and must...
Restrict the date range to 20 days
sql,oracle,oracle-sqldeveloper,date-arithmetic
Your date contains fractional seconds, so it's a TIMESTAMP not a DATE datatype. Dont worry about the TIMESTAMP format, because Oracle store it internally always as a number then it is formatted depending to your session settigs, you can use + operator to add days to any datetime datatypes (DATE,...
Sum of values from 3rd previous month
sql,postgresql,aggregate,date-arithmetic,aggregate-filter
Use date_trunc() to calculate timestamp bounds: SELECT id, default_code , (SELECT SUM(product_uom_qty) FROM sale_order_line c WHERE c.product_id = a.id ) AS "Total Sold" , (SELECT SUM(product_uom_qty) FROM sale_order_line c WHERE c.product_id = a.id AND c.create_date >= date_trunc('month', now()) - interval '2 month' AND c.create_date < date_trunc('month', now()) - interval '1...
How do I exclude weekends in SQL?
sql,oracle,datetime,date-arithmetic
You just need to add the following filter: WHERE TO_CHAR(date_column, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN') Your query would look like: SELECT SLA_Date FROM orders WHERE TO_CHAR(SLA_Date, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN') For example(the WITH clause is only to build a test case), the below query is to display only the...
Sum the differences between dates
sql,oracle,oracle11g,date-arithmetic
Try this : select sum(End_date-Start_date+1) from details_table; The sum function will sum the total of all the dates, which should give you the 13 "off days" you wanted. If you want to add the start_date/end_date conditions, you can do it like this. select sum(End_date-Start_date+1) from details_table where Start_date>=to_date('01/01/2015','mm/dd/yyyy') and End_date<=to_date('01/25/2015','mm/dd/yyyy');...
How to mask date format in sql?
sql,oracle,oracle11g,date-arithmetic
From performance point of view, I would not use TRUNC as it would suppress any regular index on the date column. I would let the date arithmetic as it is, and ROUND the value. For example, SQL> SELECT SYSDATE - to_date('2015-05-20 09:21:11','YYYY-MM-DD HH24:MI:SS') diff, 2 ROUND( 3 SYSDATE - to_date('2015-05-20...
How to get the end of a day?
sql,postgresql,date-arithmetic
Take the date, truncate it, add one day and subtract one second: select date_trunc('day', date) + interval '1 day' - interval '1 second' You can put the logic in an update if you want to change the data in the table. Of course, you can also add 24*60*60 - 1...
How to pass a parameter into a date function
sql,postgresql,parameter-passing,plpgsql,date-arithmetic
@IMSoP already shed light upon your syntax error. However, this can be simpler, faster and cleaner in multiple ways. CREATE OR REPLACE FUNCTION test(_source int, _days int) RETURNS integer AS $func$ SELECT count(*)::int FROM ad a WHERE a.source = $1 AND a.createdate::date = current_date - $2 $func$ LANGUAGE sql STABLE;...
sorting date field with datatype as varchar2 in oracle 11g
sql,oracle,oracle11g,date-arithmetic,date-conversion
You can prepend a century marker based on the length of the value: select value, case when length(value) = 4 then '19' else '20' end || lpad(value, 4, '0') as dt from t order by case when length(value) = 4 then '19' else '20' end || lpad(value, 4, '0'); VALUE...
Subtract hours from the now() function
sql,postgresql,datetime,timezone,date-arithmetic
Answer for timestamp You need to understand the nature of the data types timestamp without time zone and timestamp with time zone (names can be deceiving). If you don't, read this first: Ignoring timezones altogether in Rails and PostgreSQL The AT TIME ZONE construct transforms your timestamp to timestamptz, which...
Oracle TRUNC date by quarter but with custom year start date
Seems to me you have several issues here. 1) To push a date from December into the native Q1 bundle, use add_months(:date,1). That moves everything by your offsets into Oracle's Q1,2,3,4 for easy aggregation. 2) Labeling. If you need the query to do dynamic labeling, then APC and Lalit have...
Oracle to_date SQL formatting issue,
to_date (event_dt,'DD/MM/YYYY') Based on your reply to the comments, Your database is Oracle Your event_date column's data type is DATE. Never apply TO_DATE on a DATE column. It forces Oracle to: first convert it into a string then convert it back to date based on the locale-specific NLS settings....
How to add a running count to rows in a 'streak' of consecutive days
sql,postgresql,window-functions,date-arithmetic,gaps-and-islands
Building on this table (not using the SQL keyword "date" as column name.): CREATE TABLE tbl( pid int , the_date date , PRIMARY KEY (pid, the_date) ); Query: SELECT pid, the_date, row_number() OVER (PARTITION BY pid, grp ORDER BY the_date) AS in_streak FROM ( SELECT *, the_date - '2000-01-01'::date -...
Date/Time Difference in Oracle for same field in different rows
sql,oracle,datetime,date-arithmetic
You could use the analytic LAG() OVER() function to get the difference between the dates. For example, SQL> WITH t AS 2 ( 3 select 'O' as "MODE", 'V1234567890' as Vehicle, '1411196232' as OrderCode, to_date('2014-11-19 16:34:35','yyyy-mm-dd hh24:mi:ss') as ActionDate from dual 4 union all 5 select 'I' as "MODE", 'V1234567890'...
Match dates against date intervals (holiday periods) in multiple years
Here's another possibility using foverlaps in package data.table. library(timeDate) library(data.table) # first, some dummy data # create easter interval for 2000-2002 easter <- data.table(start = as.Date(Easter(2000:2002, -14)), end = as.Date(Easter(2000:2002))) # start end # 1: 2000-04-09 2000-04-23 # 2: 2001-04-01 2001-04-15 # 3: 2002-03-17 2002-03-31 # set key for overlap...
Jasper - Oracle - How to filter by date
oracle,filter,oracle11g,ireport,date-arithmetic
When you want to compare DATEs, you need to convert the literal into DATE using TO_DATE. No need to use LIKE operator. You could either useTRUNC on the DATE column, however, that would suppress any regular index usage. It would be better to use a DATE RANGE condition. Remember, DATE...
Detect consecutive dates in pandas series of DatetimeIndex
datetime,numpy,pandas,date-arithmetic
Try something like: data.index - data.index.shift(1, freq=pd.DateOffset(1)) per @chrisb's answer to Calculating time difference between two rows...
Oracle date between two dates
sql,oracle,oracle10g,between,date-arithmetic
Sysdate - 4 must be the first where h.Date_Created between (Sysdate - 4) and (Sysdate - interval '15' minute) ; ...
how to subtract date from date from sql in python
python,python-2.7,date-arithmetic
You can use strptime from datetime module to get python compatible date time from your query result using a format string. (You might have to play with the format string a bit to suit your case) ts = '2015-03-01T17:09:00.000+0000' to a format string like f = '%Y-%m-%dT%H:%M:%S.%f%z' date_from_sql = datetime.datetime.strptime(ts,...
Condition for current time greater than or equal to from time and current time less than or equal to time in ORACLE
sql,oracle,oracle11g,date-arithmetic
It looks like you are interested only in the time but not in the date part. Assuming data type of FROM_TIME and TO_TIME are DATE or TIMESTAMP you can do this: select * FROM SUPPLY_TIMING WHERE EXTRACT(HOUR FROM SYSDATE) + EXTRACT(MINUTE FROM SYSDATE)/60 BETWEEN EXTRACT(HOUR FROM FROM_TIME) + EXTRACT(MINUTE FROM...
Syntax for inserting just time in Oracle table
Just make it with to_date like below select to_date(emp_out,'HH:MIAM')-to_date(emp_in,'HH:MIAM') from attendance made an sqlfiddle http://sqlfiddle.com/#!4/96975/8 with the hours difference between time...
How can i get data on 1 hour basis
sql,oracle,datetime,oracle10g,date-arithmetic
You might: select ... from ... where to_char(date_time,'HH24') = '17' Place an index on the expression to_char(date_time,'HH24') and you may get improved performance, but it depends on the distribution of values within the data. If 1/24th of the rows meet the condition then an index might help, but if it's...
Count weeks between dates and present as a string the way Instagram does [on hold]
ios,objective-c,cocoa-touch,nsdate,date-arithmetic
Any date arithmetic you need to do in Cocoa (Touch) will involve NSCalendar and NSDateComponents. They can figure out for you how the differences between two dates quite easily. NSDate * originDate = ...; NSCalendar * cal = [NSCalendar currentCalendar]; NSDateComponents * weekComp = [cal components:NSCalendarUnitWeekOfYear fromDate:originDate toDate:[NSDate date] //...
MySQL Select last 7 days
mysql,sql,where-clause,date-arithmetic
The WHERE clause is misplaced, it has to follow the table references and JOIN operations. Something like this: FROM tartikel p1 JOIN tartikelpict p2 ON p1.kArtikel = p2.kArtikel AND p2.nNr = 1 WHERE p1.dErstellt >= DATE(NOW()) - INTERVAL 7 DAY ORDER BY p1.kArtikel DESC ...
Finding gaps in dates
sql,oracle,oracle11g,date-arithmetic
This self-join query produced the same output as yours: with data as ( select rownum rn, x.* from customer_wer x order by id_customer, date_from) select k.date_from, k.date_to, k.id_customer, w.date_from next_date from data k join data w on k.id_customer = w.id_customer and k.rn + 1 = w.rn where k.date_to+1 < w.date_from...
Extract hours as 1 - 48 from half hour interval times
sql,database,postgresql,date-arithmetic
Simply use formula 1 + extract(hour from 2 * tm) - it gives your expected result exactly - obligatory SQLFiddle.
Calculate previous and next months from date string
mysql,sql,datetime,hive,date-arithmetic
MySQL offers powerful, but slightly clunky, date arithmetic functions. The functions STR_TO_DATE() and DATE_FORMAT() allow translation from dates represented as text strings to the DBMS's internal DATE and DATETIME formats. If you happen to have a text column, called, for example, month, in your SQL table, with text dates of...