Questions tagged [date-math]
Use for queries that calculate dates or date ranges
21
questions
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
37 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
722 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 ...
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 ...
0
votes
2answers
175 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
168 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
51 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
77 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
2answers
98 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
1answer
78 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. ...
-1
votes
1answer
984 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!
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
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 ...
5
votes
4answers
683 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 ...
2
votes
3answers
2k 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 ...
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 ...