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).

Filter by
Sorted by
Tagged with
0 votes
0 answers
42 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,.....
101001000100001's user avatar
0 votes
1 answer
20 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 ...
Mathly's user avatar
  • 1
3 votes
5 answers
1k 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 ...
Fajela Tajkiya's user avatar
1 vote
2 answers
44 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 ...
RaGe's user avatar
  • 13
0 votes
1 answer
50 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 ...
markzzz's user avatar
  • 243
-1 votes
1 answer
28 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 ...
Annie's user avatar
  • 1
0 votes
0 answers
20 views

How I can display the thumeric value of the day of week into Greek?

I have the following database: create workers ( id SERIAL PRIMARY KEY, name STRING ); create works_zones ( id SERIAL PRIMARY KEY, area_name String, work_from TIME WITHOUT TIMEZONE, ...
Dimitrios Desyllas's user avatar
0 votes
2 answers
115 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 ...
Jeroen Budding's user avatar
-2 votes
1 answer
128 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 ...
Raj's user avatar
  • 9
-2 votes
1 answer
45 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 ...
Douwe van der Meer's user avatar
0 votes
0 answers
56 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-...
Sunny's user avatar
  • 1
0 votes
1 answer
30 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 ...
sixtytrees's user avatar
2 votes
1 answer
580 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 ...
sunShine's user avatar
0 votes
0 answers
62 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 ...
daveyjones88's user avatar
-2 votes
2 answers
138 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 ...
Wagner S.'s user avatar
0 votes
0 answers
92 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 ...
John Lang's user avatar
2 votes
1 answer
46 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 ...
Paulmcf1987's user avatar
0 votes
2 answers
93 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 ...
Zystrix's user avatar
  • 45
0 votes
1 answer
524 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 ...
waltmagic's user avatar
  • 141
-2 votes
2 answers
303 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 ...
Spindo82's user avatar
0 votes
0 answers
586 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....
XM01 - stands with Palestine's user avatar
0 votes
1 answer
38 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 = '...
PaulMcF87's user avatar
  • 167
0 votes
1 answer
39 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 ...
Ashish Thakur's user avatar
0 votes
2 answers
547 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 ...
Phil Tomlinson's user avatar
4 votes
1 answer
1k 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 ...
9001_db's user avatar
  • 43
2 votes
0 answers
133 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() ,...
user avatar
0 votes
1 answer
809 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 ...
Jane 's user avatar
-1 votes
3 answers
5k 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. ...
Hrvoje T's user avatar
  • 133
1 vote
1 answer
43 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 ...
NickR's user avatar
  • 121
0 votes
1 answer
490 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 ...
Homer's user avatar
  • 99
4 votes
2 answers
1k 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 ...
SAKA UK's user avatar
  • 90
0 votes
2 answers
4k 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 ...
sci9's user avatar
  • 101
1 vote
0 answers
51 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 ...
RBS's user avatar
  • 123
1 vote
1 answer
391 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 ...
RBS's user avatar
  • 123
-1 votes
1 answer
664 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 ...
showkey's user avatar
  • 284
1 vote
1 answer
163 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 ...
rafamaniac's user avatar
0 votes
1 answer
224 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("...
Dolphin's user avatar
  • 755
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 (...
Beefstu's user avatar
  • 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 ...
kris's user avatar
  • 13
0 votes
1 answer
454 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....
kris's user avatar
  • 13
1 vote
1 answer
1k 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 &...
Dmitry K.'s user avatar
  • 143
2 votes
1 answer
467 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 ...
intel_chris's user avatar
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: ...
ZedRed's user avatar
  • 43
0 votes
1 answer
46 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?
attoslins's user avatar
0 votes
1 answer
50 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. ...
r_j's user avatar
  • 111
0 votes
1 answer
71 views

Creating a daily Search Query for the previous day to today

I am trying to make a custom search query in maximo for equipment at work. I have a specific time range I want but it is always for the day before. Example: Today is 8/26/2021. I want a query for 8/25/...
LaxMack's user avatar
4 votes
1 answer
476 views

SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before

In one of stored procs I need to capture the duration of a live event. It is guaranteed to never exceed 24 hours, so the datatype for the result column is chosen as TIME(3) (mapping to TimeSpan on ...
Astrogator's user avatar
0 votes
1 answer
84 views

Strange replication failure on date field under MySQL 5.7

This problem cropped up yesterday and I'm quite stumped. I have a primary RDS cluster that is running MySQL 5.6 and RDS is forcing everyone to 5.7 soon. I have a smaller standalone replication slave ...
Machavity's user avatar
  • 103
0 votes
0 answers
46 views

using lead() to modify next row data

So I have some data like this: And what I would like to do is modify the WorkDate using the following logic if lead(EndTime) = StartTime then lead(WorkDate) = WorkDate So for example R3 WorkDate ...
Freud's user avatar
  • 1
0 votes
1 answer
734 views

How does CONCAT format date fields?

I have the following code : start transaction; show datestyle; set datestyle to DMY; show datestyle; select concat(timestamp_field, '') from my_table ; commit; It outputs this : ...
Stephan's user avatar
  • 1,443

1
2 3 4 5
7