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
1 answer
22 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
1 answer
28 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
1 answer
45 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
59 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
61 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 votes
3 answers
522 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. ...
  • 133
1 vote
1 answer
31 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
1 vote
1 answer
143 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 ...
  • 111
4 votes
2 answers
524 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 ...
  • 88
0 votes
2 answers
806 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
33 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
39 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
208 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 ...
  • 188
0 votes
0 answers
10 views

password changed date of Mango DDB users

How can I find the password_change_date of users in MangoDB? Can I get a query to get this info.? For example in Oracle, we can gather it from dba_users table by selecting username, ...
  • 1
1 vote
1 answer
62 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 ...
0 votes
1 answer
48 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("...
  • 527
0 votes
1 answer
122 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
700 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
100 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
314 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 &...
  • 123
-1 votes
1 answer
139 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 ...
1 vote
0 answers
282 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
41 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?
0 votes
1 answer
32 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
0 votes
1 answer
25 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/...
4 votes
1 answer
263 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 ...
0 votes
1 answer
37 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 ...
  • 103
0 votes
0 answers
36 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 ...
  • 1
0 votes
1 answer
105 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 : ...
  • 1,333
0 votes
1 answer
59 views

Get sequence value date?

I have an OOB system that has hundreds of tables. Each table has a ROWSTAMP column (integer) that gets populated by a trigger each time a record is created/updated. All the tables & triggers in ...
  • 1,387
1 vote
1 answer
1k views

Calculate days between to dates postgresql

I'm having a problem: I'm trying to calculate days between two dates. Let's say I have the date '2005-05-25 06:04:08' and I want to count how many days are from that day to the current date. I tried ...
  • 11
1 vote
3 answers
123 views

String concatenation and DATE/Number ordinals in one string?

How can I combine functions with strings to end up with a whole in one sentence? I mean something like this (expected result): Today is the 3rd day of the year, the 2nd week of the year and the 1st ...
  • 11
0 votes
2 answers
213 views

Create Date Spans from Contiguous and Non-Contiguous Date Spans

I am trying to create a query that will return a series of dates based off of the 'BEG_DT' and 'END_DT'. The results should show a series dates based off of the 'BEG_DT' and with a span between each ...
  • 27
2 votes
1 answer
188 views

Group By Jalali Date in Postgresql

I want GROUP BY jalali's month, all date save as gregorian format in my database, I found following sql query but group by gregorian date SELECT COUNT(reserverow.id), EXTRACT(MONTH FROM reserverow....
0 votes
2 answers
280 views

Does a NULL DATE use as much storage as a value?

I need to store a DATETIME value that may or may not have a date. My options are: | starts DATETIME | |---------------------| | 0000-00-00 12:38:06 | | starts_date DATE | starts_time TIME | |----...
  • 121
5 votes
1 answer
4k views

Parsing DATE while copying csv file into PostgreSQL table

I have a long series of .csv files, which I want to import into a local database. I believe my query is correct, but there are some problems in parsing DATE and TIMESTAMP columns. PostgreSQL reads ...
0 votes
2 answers
304 views

Get data between date range

I have a range of tabular data with two columns for dates Start Date and End Date and I want to pick up everything either Starts/Ends on coming Monday 03/05/21 or Starts/Ends on Sunday 09/05/21 and ...
0 votes
2 answers
23 views

Count results of table by date [closed]

I am trying to query the number of transactions that occur on a given day with the following query select COUNT(seq_no) from "table" where start_time >= '2020-04-01 00:00:00' and ...
  • 75
0 votes
1 answer
17 views

Can you change the value according to the date? [closed]

Is there a formula that can change the value according to the date of the day? Examaple: in April it's 10 and in November it's 5, August is 6. Kind regards Karsten
2 votes
1 answer
229 views

Add a number of days to a timestamp

I'd like to add (or rather subtract) a number of days from a timestamp. For example from 2020-04-13 00:00:00 Europe/Berlin I want to subtract 20 days and I expect the result to be 2020-03-24 00:00:00 ...
  • 497
0 votes
2 answers
767 views

How to compare time portion of dates for multiple dates

I have a postgresql database with various records which have a date column which looks like 2020-10-12 12:45:55. I am wanting to construct a query that looks for activity with timestamps that are on ...
  • 105
-1 votes
1 answer
488 views

Match closest date from a table to a table with just dates

I am trying to feel some gaps in table with values from a previous date. I have created a date table which looks like this: date 2021-04-13 2021-04-14 2021-04-15 2021-04-16 2021-04-17 2021-04-...
5 votes
2 answers
1k views

How to change an incorrect datatype for a birth date field from VARCHAR to DATE

I made a mistake when creating my users table with a birthdate field and instead of putting the DATE datatype, I put a VARCHAR! So now my users table looks like this: CREATE TABLE IF NOT EXISTS users ...
1 vote
1 answer
153 views

3 Simple Queries SQL Student Question, Query Errors in Microsoft Access

The First Question is: "List the name of each trip that does not start in New Hampshire (NH)." SELECT TripName FROM Trip WHERE State!='NH'; /* works with State='NH' */ The Second Question ...
1 vote
1 answer
1k views

SSIS convert date in mmddyyyy format to date format using SSIS expression

I'm receiving dates in a mmddyyyy format but want to convert it to the date format in sql/ssis yyyy-mm-dd, how can I do so using an SSIS expresion. example of dates I'm reciving are 03051978 but want ...
1 vote
1 answer
21 views

Return only records with a date after the most recent September 1st

I have a table with data with a date column called "dtAssessmentDate" in a table called "tblAssessmentRecords". What I want to achieve is for a select query to return only the ...
0 votes
1 answer
120 views

SQL Only display data with Max latest date date

tracking_id | date_action | action_id 1 | 2021/01/24 | 4 1 | 2021/01/26 | 3 2 | 2021/01/25 | 67 2 | 2021/01/24 | 8 4 | 2021/01/27 | 10 5 ...
-1 votes
1 answer
463 views

Increment date by 1 day based on another column's date from previous record

I am using SQL server 2008 R2 and I don´t have LAG and LEAD functions. I have this inicial table and I need that StartDate is equal to EndDate from previous record plus 1 day. See Result Table. ...
-2 votes
1 answer
3k views

How do convert Gregorian date to Persian Date in sql server?

For example, I want to convert the Gregorian date in Query below to the Persian date select convert(GregorianDate ,persianDate) date from TOrder where orderid=? result: date: 2021-01-25 ----->...
2 votes
2 answers
50 views

Can I change MSSQL interpretation of date order system wide?

I use Laravel with PHP from https://laravel.com/ MSSQL is a driver and works perfectly - with th exception of dates. For some reason the project has decided that the follwoing is a universal date ...

1
2 3 4 5
7