Questions tagged [date-math]

Use for queries that calculate dates or date ranges

Filter by
Sorted by
Tagged with
0
votes
1answer
50 views

count of rows in table day wise

I need to get a count of rows in a table day wise. The table looks like below: Table ABC: f_id|reg_date 1|2020-09-08 2|2020-09-12 3|2020-10-01 4|2020-09-07 5|2020-09-08 6|2020-09-09 Expected output ...
1
vote
1answer
35 views

Derive Date Spans from Start and End Dates in SQL Server table

I am using SQL Server 2016 I have a table that contains 1 row per month that a patient is assigned to a particular Provider. A patient can be assigned to multiple providers during the year. How can I ...
2
votes
2answers
715 views

How to add a day/night indicator to a timestamp column?

There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I only get 'DAY' as outcome. Am I not formatting the ...
0
votes
2answers
90 views

Datediff SECOND with DATETIME type and rounding returns questionable results

I am puzzled by the following behaviour in SQL Server (tested on SQL Server 2017). The goal is to return the number of seconds between two different datetime values. The two datetimes typically lie ...
0
votes
1answer
36 views

SQL Query- field calculation based on date

I'm writing a query for sales targets per week, however I'm getting a little stuck on this. Targets are per month, per year, so for example, Brand A has a monthly target (changes each month) The ...
-1
votes
1answer
124 views

Query to get yesterday date with specific amount of time

I am struggling to query for a specific time frame between 21:50 and 21:59: SELECT Username, EventTime, EventsEx.FirstName, EventsEx.Surname, PeripheralName, EventTypeDescription, CardNumber ...
0
votes
1answer
82 views

Sum of values each hour at half past (that hour)

I need to aggregate the "sum" of values of a table with TS, Value columns of each half past hour (of 60 minutes), i.e. 00:30, 01:30, 02:30, etc Sample data: Val TS 1 2019-08-12 00:00:00.013 3 ...
0
votes
0answers
40 views

Nested Sum if statement with DateDiff

Problem: Need to account for delays in review process. The field "EC2WL" is when an employee submitted a case to the Work Leader for review and the field "WL1stReview" is when the Work Leader first ...
0
votes
1answer
19 views

DATE_SUB with configurable value

In MySQL you can use the DATE_SUB function to get a new date based on the values passed in. For example I can use the following to get the a date of 30 days ago from when I run the function SELECT ...
3
votes
2answers
172 views

Merge Only Events that Start immediately after the Previous Event without any Gap

How can i merge only the dates occur subsequently.? (i.e, The Next Event begins immediately after the previous event completed). Here, we need to consider some other Columns also for getting the ...
2
votes
2answers
65 views

Filter Table by a Historical 'Last Day of the Month'

I have a daily appending table, that I need a formula for that would filter the table results to just the last day of any given historical month. For example: Table: DwDate Balance ...
0
votes
1answer
73 views

Converting MySQL 5.5 datetime binary to MySQL 5.6 (math problem)

I received a database "backup" of binary files (.frm and .ibd) that were copied from a MySQL 5.5 install. Unfortunately, I was unable to get these files to import into a MySQL 5.5 vm that I created. ...
0
votes
2answers
91 views

How can I use mysql to determine the Sundays for the last year?

I have a table where I'm currently aggregating ~200 rows per day of stats. These stats include things like # of users, how many people using X feature, etc. What I'd like to do is query this table ...
0
votes
2answers
31 views

Find Events between Dates

I have table with following data id start_date end_date 1 2018-01-01 2018-01-15 2 2018-01-11 2018-01-16 3 2018-01-03 2018-02-10 4 2018-01-01 2018-03-15 I want to list events based on ...
-1
votes
1answer
925 views

How do I exclude weekends (SQL) [closed]

I have different Year, Month and Day columns, And I wish to exclude weekends. How can I do so? Thank you very much!
1
vote
1answer
1k views

Split datetime records that fall between 10pm to 7am into new record [closed]

I am very new to SQL Server. I am trying to split time in time out records that fall between night shifts from a employee shifts table but I am having a difficult time doing so. Night Shift runs from ...
5
votes
3answers
3k views

How do I round time to the upper multiple of an arbitrary time interval?

Example: If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '5 minute', then the desired output is 2018-05-17 22:50:00. If the current time is 2018-05-17 22:45:30 and the ...
2
votes
3answers
1k views

Multiply and divide dates

I had a recent DBA.SE question that involved dates that were used as the x-axis in a graph: Interpolate dates along a line. It was centred around a road_condition table: COND_ID ROAD_ID COND_DATE ...
5
votes
4answers
643 views

Interpolate dates along a line

I have a road_condition table in Oracle 12c: create table road_condition ( cond_id number(5,0), road_id number(5,0), cond_date date, condition number(5,0) ); insert into ...
7
votes
1answer
2k views

How does date math work in SQL Server?

I often see queries written with DATEADD and DATEDIFF in the WHERE clause to define a range, flatten datetime to 0 hours, or find the last day of a month or year, but I don't understand how all the ...
1
vote
2answers
1k views

number of seconds to years-months-weeks-days-HH:MM-SS

I am running this all the time to get HH:MM:SS from a number of seconds: DECLARE @s INT SELECT @s = 23251 SELECT @s , CONVERT(TIME, DATEADD(SECOND, @s, 0)); when I want to add days to the ...