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
1 vote
1 answer
30 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 ...
user avatar
  • 111
0 votes
1 answer
177 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 ...
user avatar
  • 101
1 vote
0 answers
29 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 ...
user avatar
  • 123
1 vote
1 answer
31 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 ...
user avatar
  • 123
-1 votes
1 answer
53 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 ...
user avatar
  • 126
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, ...
user avatar
  • 1
1 vote
1 answer
42 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 ...
user avatar
0 votes
1 answer
28 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("...
user avatar
  • 477
0 votes
1 answer
50 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 (...
user avatar
  • 103
1 vote
1 answer
129 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 ...
user avatar
  • 13
0 votes
1 answer
53 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....
user avatar
  • 13
1 vote
1 answer
135 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 &...
user avatar
  • 123
-1 votes
1 answer
55 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 ...
user avatar
1 vote
0 answers
42 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: ...
user avatar
  • 43
0 votes
1 answer
40 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?
user avatar
0 votes
1 answer
29 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. ...
user avatar
  • 111
0 votes
1 answer
22 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/...
user avatar
4 votes
1 answer
196 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 ...
user avatar
0 votes
1 answer
31 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 ...
user avatar
  • 103
0 votes
0 answers
35 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 ...
user avatar
  • 1
0 votes
1 answer
44 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 : ...
user avatar
  • 1,253
0 votes
1 answer
43 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 ...
user avatar
  • 1,373
1 vote
1 answer
632 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 ...
user avatar
  • 11
1 vote
3 answers
81 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 ...
user avatar
  • 11
0 votes
2 answers
152 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 ...
user avatar
  • 27
1 vote
1 answer
121 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....
user avatar
0 votes
2 answers
144 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 | |----...
user avatar
  • 121
4 votes
1 answer
3k 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 ...
user avatar
0 votes
2 answers
181 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 ...
user avatar
0 votes
2 answers
21 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 ...
user avatar
  • 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
user avatar
1 vote
1 answer
97 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 ...
user avatar
  • 415
0 votes
2 answers
165 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 ...
user avatar
  • 105
-1 votes
1 answer
183 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-...
user avatar
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 ...
user avatar
1 vote
1 answer
120 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 ...
user avatar
1 vote
1 answer
777 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 ...
user avatar
1 vote
1 answer
17 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 ...
user avatar
0 votes
1 answer
84 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 ...
user avatar
-1 votes
1 answer
179 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. ...
user avatar
-3 votes
1 answer
2k 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 ----->...
user avatar
2 votes
2 answers
48 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 ...
user avatar
0 votes
1 answer
948 views

postgresql copy comand with date in filename

I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12). COPY (SELECT * FROM mytable) TO 'C:\Users\me\mylocation\filename_date_time.csv' ...
user avatar
0 votes
1 answer
224 views

Warning: #1292 Incorrect datetime error when using DATE_ADD()

Doing this works fine: SELECT * FROM table WHERE foo < '2021-01-09 00:00:00' But this one: SELECT * FROM table WHERE DATE_ADD('foo', INTERVAL 5 DAY) < '2021-01-09 00:00:00' results in Warning: #...
user avatar
  • 159
0 votes
1 answer
290 views

Select count of timestamp formatted field in short date format to give number of rows

I have a field titled DTUpdated and it is a timestamp field. I am trying to select a count of the dates in the rows that are xx old. i.e. 0-3 months, 3-6 months etc. I am not at the writing date ...
user avatar
0 votes
2 answers
58 views

convert date in string format to date datatype sql [duplicate]

I have values in a column named Date as a nvarachar data type in the form of mmddyy and want to convert the values to a date datatype in the form of yyyy-mm-dd. What sql colde can I use to convert the ...
user avatar
1 vote
0 answers
8 views

Force use of System.Date in SSAS

I would like to know idf it is possible to force System.Date use in an SSAS data source view. For now, I have a view with a field of type Date and in the data source view it is converted to a System....
user avatar
  • 141
0 votes
1 answer
18 views

Verify day falls in month

I have a table in BigQuery with Xday, Xmonth, and Xyear columns but the data was user generated and apparently done without adequate input validation so some of it is nonsense. It's easy enough to do ...
user avatar
  • 115
0 votes
2 answers
59 views

use of age() function postgres [closed]

I am trying to calculate the time since an event took place. The event timestamp will itself be the result of a MIN aggregate function. Is there a way to do this. I have run a query to get the ...
user avatar
-3 votes
1 answer
37 views

select rows where today is between dates held in table

using sqlite (although I would also like to do it with SQLServer at a later date) I have a table which contains the start date of an exchange rate. I want to select row where a the start date is ...
user avatar

1
2 3 4 5
7