Date-difference will give difference in the number of days, months, years etc. between any two calendar dates. Not defined by ISO/ANSI SQL, but several vendors have similar implementations.
Questions tagged [datediff]
1599 questions
6
votes
2 answers
How to calculate difference between two datetime in ElasticSearch
I'm working with ES and I need a query that returns the difference between two datetime (mysql timediff), but have not found any function of ES to do that. Someone who can help me?
MySQL Query
SELECT SEC_TO_TIME(
AVG(
TIME_TO_SEC(
…
fuzeto
- 207
- 1
- 3
- 8
6
votes
4 answers
Sql Server 2008 Calculate nights between two dates but remove 31st night
I have a booking table with the following information:
BookingID,(unique, not null)
StartDate, (not null)
EndDate (not null)
I need to calculate the number of nights someone remained in residence which I can do with a DATEDIFF between EndDate and…
JulieMelinda
- 63
- 3
6
votes
4 answers
SQL DATEDIFF Not working?
I am running a simple DATEDIFF query but it doesn't seem to calculate the days properly or i'm doing something wrong.
If I run
PRINT DATEDIFF(Day, 2010-01-20, 2010-01-01)
RETURN 19
Which is correct. If i change the month in the first date to Feb…
Jammer
- 2,330
- 11
- 48
- 77
6
votes
2 answers
DATEDIFF() or BETWEEN for Date Ranges in SQL Queries
I have recently been informed that the use of the BETWEEN method in SQL is somewhat unreliable, and I should therefore be using DATEDIFF(). However, another programmer has informed me this is not the case and the BETWEEN method works brilliantly in…
Ben Carey
- 16,540
- 19
- 87
- 169
6
votes
2 answers
Difference between two dates, including only business days (i.e. excluding weekends and holidays)
How can I get the number of business days between two java.util.Date, i.e. excluding weekends and holidays? By holidays, I mean legally recognized holidays. It must be depending on the country, because of the holidays that are different from a…
sp00m
- 47,968
- 31
- 142
- 252
6
votes
3 answers
Updating year in datetime variable
I'm playing around with manipulating a datetime variable. I can't seem to find a way to update a datetime variable year to the current year.
For example I have
2007-12-01 00:00:00.000
But I would like that to be
2012-12-01 00:00:00.000 (The…
JGreasley
- 301
- 4
- 12
6
votes
5 answers
How do you handle NULLs in a DATEDIFF comparison?
I have to compare 2 separate columns to come up with the most recent date between them. I am using DATEDIFF(minute, date1, date2) to compare them, however, in some records the date is Null, which returns a null result and messes up the CASE.
Is…
user1385330
- 61
- 1
- 1
- 2
5
votes
5 answers
SQL Query to check if today is Employee's birthday
Can any one tell me how to get the list of an employee who have their birthdays today ....
Thanks,
Vishal
Vegeta
- 291
- 2
- 7
- 20
5
votes
4 answers
python: difference of two timedate strings
I have two date strings (taken from user input and can vary greatly)
s1 = '2011:10:01:10:30:00'
s2 = '2011:10:01:11:15:00'
I wish to find the difference between the two as minutes.
How should I proceed to tackle this ?
Simply Seth
- 3,246
- 17
- 51
- 77
5
votes
2 answers
What does absolute parameter do in DateTime::diff(DateTime [, bool absolute = false])
Here is the sample code
$c = new DateTime();
$o = clone $c;
$o->modify('-60 days');
$diff = $c->diff($o);
$diff2 = $c->diff($o, TRUE);
var_dump($diff, $diff2);
which outputs
object(DateInterval)#3 (8) {
["y"]=> int(0), ["m"]=> int(1), ["d"]=>…
wadkar
- 960
- 2
- 15
- 29
5
votes
4 answers
Battling Datediff in SQL
I am writing a little query in SQL and am butting heads with an issue that it seems like someone must have run into before. I am trying to find the number of months between two dates. I am using an expression like…
JBone
- 3,163
- 11
- 36
- 47
5
votes
2 answers
how to resolve function datediff(unknown, timestamp without time zone, timestamp without time zone) does not exist
SELECT "reviewedAt", "createdAt", DATEDIFF('hour', "createdAt"::timestamp, "reviewedAt"::timestamp) as hours_approved from "yadda$prod"."Application"
error [42883] ERROR: function datediff(unknown, timestamp without time zone, timestamp without…
ChatGPT
- 5,334
- 12
- 50
- 69
5
votes
6 answers
DATE_DIFF but only counting business days
I have a fairly simple query; it looks as such:
SELECT
order_date,
pickup_date,
DATE_DIFF(pickup_date,order_date, day) order_to_pickup
FROM
`orders.table`
The only is issue is, I need to be calculating the date difference in BUSINESS days, not all…
user11052700
5
votes
2 answers
DATEDIFF in DB2 query
I have a query from mysql that has been running on a table I recently migrated to DB2.
The query now fails on DB2 due to the line below, saying that DATEDIFF cannot be found. I'm assuming only because this isn't a valid function on db2.
Is there an…
Geoff_S
- 4,917
- 7
- 43
- 133
5
votes
4 answers
Oracle equivalent to SQL Server/Sybase DateDiff
We are now using NHibernate to connect to different database base on where our software is installed. So I am porting many SQL Procedures to Oracle.
SQL Server has a nice function called DateDiff which takes a date part, startdate and enddate.
Date…
David Basarab
- 72,212
- 42
- 129
- 156