Questions tagged [date]
Questions related to date or date-related functions, data-types, representations (how dates might be stored and displayed) and operations (such as subtracting dates, adding intervals to dates).
350
questions
8
votes
2
answers
1k
views
Is an invalid date considered the same as a NULL value?
I am using MySQL 5.7 and I have notice something which I cannot explain to myself with my current knowledge so here it goes.
Is an "invalid" date 0000-00-00 considered same as NULL for a ...
- 1,888
-2
votes
1
answer
61
views
Why is it selecting date from another month that requested one? [closed]
I have a table that store time begin and time end of some events. I want to search in this table all even that were occuring during specific day.
Here is example data:
Id
Date begin
Date End
Name
1
...
- 181
0
votes
3
answers
44
views
what's the bests practices in date
What's the best practices with a date field when you want to fill in the fact that it's not filled in and not leave it NULL?
should you put 1999-01-01, 9999-09-09, 1890-12-31...?
Are there any rules ...
- 113
0
votes
0
answers
35
views
Interest Accrual and insert monthly records since start date
I need to calculate the (1) "To" date after the initial contribution amount to accrue for accounting interest. The "TO" is calculated as
End of month date if no other are events ...
1
vote
2
answers
146
views
How to generated missing dates between date range that do not exist in the joined table in CockroachDB?
I have Table A and Table A which have
Table A
id, start_date, end_date
Table B
id, table_a_id, archive_date
I want to return the dates that are missing in Table B, the dates that are NOT in the ...
- 23
0
votes
1
answer
108
views
How to generate most recent nth day of the month?
Simple question, but I haven't found any examples of this on Stack Exchange:
I need to be able to select all records in the current billing cycle, that's it. The catch is the billing cycle doesn't ...
- 143
0
votes
0
answers
60
views
Complex day-to-day difference of historical data in SQL server
Setting:
I have a table (let's call it "HistoricalData") with 10 attribute columns, a date (in string format) and a value. The table has a unique clustered index on (date, idcol1, idcol2,.....
0
votes
1
answer
22
views
I am getting an infinite fetch duration when adding a similar third left join
I am using MySQL on Windows 10.
The query below should result in three groups of data sets that are different only in the date range. It works for any two date ranges but when I try to run all three ...
- 1
3
votes
5
answers
2k
views
Optimizing SQL Server Query When Joining Tables on DATETIME column
I am working with SQL Server and I have two tables, table1 and table2. Both tables have a DATETIME column denoted as dt. I need to join these tables based not only on certain conditions but also to ...
- 1,199
1
vote
2
answers
53
views
TSQL count 2 different columns by using number table
I am trying to count 2 different date columns on a monthly interval by using a number table:
numbers table:
num
0
1
2
3
Originating Table (Example):
Gender
Subscription_Date
Cancel_Date
male
...
- 13
0
votes
1
answer
91
views
How to join matching Date fields as intervals?
Let say I've a table with a list of id/year/month (two years + 24 months for semplicity here):
Code Year Month
MAT000000001 2022 1
MAT000000001 2022 2
MAT000000001 2022 ...
- 243
-1
votes
1
answer
29
views
sql - how do I pick up all services that are open and latest of those ended - complicated
I am having issue capturing all rows that I need. A client can have number of services including same service again if that is an additional help, all services ended will have an end date and all open ...
- 1
0
votes
2
answers
430
views
SQL Count Value if the data is lower then current date
I want to verify the compliance by month. I already have the below query.
Only add the moment I am getting the following information
Only I would like to retrieve the following information add the ...
-2
votes
1
answer
261
views
Daily average from a 15mins interval datetime column o
I want to convert this 15 minutes interval data into daily average such that in the output file, I should have only two columns 'Date' and 'Daily_Avg_Value'.
This is my first post here - really wish ...
- 9
-2
votes
1
answer
111
views
How to calculate the age of a person in a string field with multiple date formats? [closed]
Bumped into a post of 9gag where someone made some fun of someone's coding (or just a joke of a coder). My question is more of a serious note, hypothetical speaking under set conditions, could that be ...
0
votes
0
answers
79
views
How to show all hours even no data in Firebird?
I want to write a query that shows all hours (for getting total order) between start date and end date even no data.
Result should be like this:
count
extract(hour)
date
0
1
2020-12-03
10
2
2020-12-...
- 1
0
votes
1
answer
65
views
how toget ordinal number of a day from beginning of year in postgresql?
I have a table like
id
created_at
1
2022-03-01
2
2022-03-23
The result should be
id
days_from_the_beginning_of_year
1
60
2
82
I can create a table like
date
...
- 103
2
votes
1
answer
908
views
How do I get the date next week in SQL (Firebird)?
I want to get a date using an SQL statement. For instance, if the date is '2018-08-07' output this should return: '2018-08-14' (one week later). Then I'm going to get the total rows between these ...
- 35
0
votes
0
answers
96
views
Overlap on Max/Min dates
I have data in a table that has gotten cut up with multiple start and end dates. This is due to an error that has happened a few times in the last year. Apparently causing the system to not see the ...
- 11
-2
votes
2
answers
172
views
Making SQL give age brackets on command
I'm trying to make SQL return how many users are inside each age bracket grabbing the date of birth from the user from the table GUEST and converting it into years, at the moment I'm trying to make it ...
0
votes
0
answers
124
views
MySQL select until 3 days of rows are met
I am trying to select dates including today through + 3 days but when nothing exists for a day, continue until 3 days is met, but do not go further than 3 days. I feel like I'm on the right track, but ...
2
votes
1
answer
51
views
Displaying items between 2 dates
I am creating a commission tracker for a sales team
Commission is payable on a new account on any business brought in for 3 months from the date of the first sale.
The accounts table will have the ...
- 43
0
votes
2
answers
115
views
MySQL YEARWEEK function is not using index
Given are two tables:
create table table_a
(
id_k mediumint unsigned not null,
id_m tinyint unsigned not null,
id_a char(10) not null,
v tinyint unsigned not ...
- 45
0
votes
1
answer
895
views
Incorrect datetime str_to_date INSERT SELECT
I have never run into such an issue before. Was given a bunch of data and told to compile it into some tables and develop some reports. The users who gathered the data used various different date ...
- 141
-2
votes
2
answers
360
views
Next 90 day review date (Teradata)
Teradata Related question… I have a table containing new starters along with a start date, every 90 days from the start date I need to conduct a review with the starter
How do I identify the next 90 ...
- 1
0
votes
0
answers
750
views
update primary key to a new one in sql server
I have got a table that has four columns and the Id is a Primary key:
Id
Matricule
Date
Code
G00183|2022-04-30|30
G00183
2022-04-30 00:00:00.000
30
G00183|2022-04-30|5010
G00183
2022-04-30 00:00:00....
- 292
0
votes
1
answer
80
views
Select entries with today and tomorrows date
I am using MariaBD and am trying to filter entries with todays date and tomorrows date.
I have managed to filter todays date using
SELECT *,
DateTime AS Time
FROM table
where status = '...
- 167
0
votes
1
answer
43
views
Finding the latest subscription date
I need to query a users database that contains record based on their subscription to a service. A user can have subscribed to it while buying the main product or have it added to their subscription ...
0
votes
2
answers
1k
views
How to Select The Lowest Date in one field that's higher than the date in another field
The below aim isn't actually what my query is for, but I'm using it as an analogy to explain more simply what I am trying to achieve:
I am trying to build a BigQuery Script which looks at all arcade ...
4
votes
2
answers
2k
views
Aggregate overlapping date intervals
I have a table with some accounts, and their subscription's start and end dates. However, these subscriptions overlap sometimes and I need the start and end dates of every connected subscription ...
- 43
2
votes
0
answers
166
views
Past date shows for CURRENT_TIMESTAMP and GETDATE() and GETUTCDATE()
Why is that that suddenly in SQL Server Management Studio when I query CURRENT_TIMESTAMP and GETDATE() and GETUTCDATE(), it gives me a past date?
SELECT
SYSDATETIME() ,SYSDATETIMEOFFSET() ,...
victorio
0
votes
1
answer
1k
views
Find Consecutive Date in SQL using 2 date columns
I am trying to group by all consecutive patient admission to the table. If the patient re-admitted to the hospital next day( or within 24 hours), then I need to keep only one admission stay per ...
- 1
-1
votes
3
answers
6k
views
How to select Max date that is valid?
I have a date as varchar (dont ask why). I need to select Max(date). However, date can be faulty, like 2034-34-34. I would like to select max date that is valid and not greater than today. It's t-sql.
...
- 163
1
vote
1
answer
61
views
Database Design: Year of Birth /Year of Death best practice - Postgres
I have a table of people (authors), and columns for Year_of_Birth and Year_of_Death. These are integers, as I wish to be able to compute things like current_age and age_at_death and ...
- 121
0
votes
1
answer
646
views
Why does PostgreSQL keep resetting my date to the wrong "timezone", with "seconds", as I try to remove it?
I asked this question at first: https://stackoverflow.com/questions/71979003/why-does-strtotime-get-confused-about-timezones-instead-of-ignoring-them-an
I have this timestamp with timezone column ...
- 99
4
votes
2
answers
2k
views
Why does this query fail when I run it from a SQL Server job?
I know how to solve the issue, but I don't understand the technicality of why the below query would work when I run it from SSMS, but the same query fails when I add it as a job in the SQL Server ...
- 100
0
votes
2
answers
5k
views
Postgres: How to automatically fix date/time field value out of range inside the query
How one can make Postgres treat a little stupid about the date which actually does not occur on the Gregorian calendar?
SELCET date, value
FROM table
WHERE date BETWEEN 2022-01-01 AND 2022-02-31
...
- 101
1
vote
0
answers
64
views
Splitting time period into multiple with irregular length, grouped by name
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 123
1
vote
1
answer
639
views
Splitting time period into multiple smaller ones with irregular length
We have a set of data containing event dates, other event data and the start and end date that of the 'event season' that the event took place in.
The issue is as follows: In postgresql, I wish to ...
- 123
-1
votes
1
answer
755
views
How to store date with only four digital format?
In postgresql the date format is yyyy-mm-dd by default.
create table sample(
quant int,year date);
The data file is stored data.csv in csv format such as:
quant,year
10,2019
12,2020
13,2021
Try to ...
- 334
1
vote
1
answer
200
views
Role-playing Dimensions
I am new to data warehouse schema and i am confused about role-playing dimensions. i have searched a lot and regarding to dates in data warehouse, it is always the same type of example they give ...
- 13
0
votes
1
answer
297
views
how to convert a string to date in MySQL 5.7
Now I have a data string like this 2002年05月11日, I want to convert this string as a unix date timestamp, what should I do to convert this time? I have tried this way:
select STR_TO_DATE("...
- 815
0
votes
1
answer
2k
views
Oracle date or timestamp PARTITION columns
Is there a query I can issue to tell if a PARTITION column is a date or timestamp.
For example this should retun timestamp
CREATE TABLE ts (
num_val NUMBER(6),
ts_val TIMESTAMP)
PARTITION BY RANGE (...
- 103
1
vote
1
answer
2k
views
SQL query display # of events per day between two time ranges
I have the following table:
id start_date end_date status
----------------------------------------------------------------------
1a 2020-04-09 21:34:00.000 ...
- 13
0
votes
1
answer
537
views
SQL query that is able to count days based on event queries
I have a table with the following data:
id date_changed color_start color_end
-------------------------------------------------------------------
1 2020-05-27 16:33:52....
- 13
1
vote
1
answer
2k
views
Postgres find gaps between one date range and others
My question is similar to https://stackoverflow.com/a/41267928/2585154 , but in my case I need to divide/split date range by multiple date ranges, not only one.
In my case is also guaranteed that &...
- 143
2
votes
1
answer
614
views
Is there a Rust crate that implements the SQL date and interval types?
I went looking for a Rust crate (on lib.rs) that implements DateTime and found chrono.rs. However, I have two issues.
I cannot find a function that takes a string representing an SQL date and ...
- 141
1
vote
0
answers
1k
views
Filling gaps in result with empty data
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Hello All,
I'm a bit stuck on this one.
Working SQL
Currently I am using this query:
...
- 43
0
votes
1
answer
49
views
MariaDB 0th day?
Today is the 2nd of october, I added a line to my table using curdate() -2 and now it shows the 0th of October when it's supposed to show September 30th. Anyone know why this is happening?
- 35
0
votes
1
answer
61
views
Add an auto now field to an existing table
I want to add a new column created_datetime. This column will automatically be filled with a the current date/time value when inserting a new row.
How should I handle the value for existing rows. ...
- 111