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
1answer
36 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
1answer
22 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. ...
0
votes
1answer
21 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
1answer
128 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
1answer
29 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 ...
0
votes
0answers
31 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 ...
0
votes
1answer
32 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 : ...
0
votes
1answer
35 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
vote
1answer
134 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 ...
1
vote
3answers
58 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 ...
0
votes
2answers
117 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 ...
1
vote
1answer
62 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
2answers
68 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 | |----...
0
votes
0answers
30 views

Plsql query to split data based on startdate and end date

I want to split data based on start ,end date ,date range configured (yearly/monthly/weekly/quarterly) For example if the Startdate is 2015/10/02 and the Enddate is 2015/12/22 and my date range is ...
2
votes
1answer
1k 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
2answers
149 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
2answers
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 ...
0
votes
1answer
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
1
vote
1answer
58 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 ...
0
votes
2answers
37 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 ...
-1
votes
1answer
39 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
2answers
805 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
1answer
48 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
1answer
305 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 ...
0
votes
0answers
29 views

store multiple date ranges in solr field

Every day I check which events happened. Each event is represented as a document in Solr index. An event can happen 4 days in a row, skips a day and then happen the next 4 days and so on... In a ...
1
vote
1answer
16 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
1answer
51 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
1answer
33 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. ...
-4
votes
1answer
629 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
2answers
46 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 ...
0
votes
1answer
417 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' ...
0
votes
1answer
139 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: #...
0
votes
1answer
112 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 ...
0
votes
2answers
47 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 ...
1
vote
0answers
7 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....
0
votes
1answer
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 ...
0
votes
2answers
45 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 ...
-3
votes
1answer
31 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 ...
1
vote
1answer
368 views

MySQL - Query records between yesterday and today but with a specific time

I seem to be having a case of the Fridays. I am new to MySQL (few weeks now) and I just can't seem to get a report automated. I need to be able to query data from yesterday 8 am until today 8 am. I am ...
-1
votes
2answers
17 views

casting custom string to date in SQL

I have a column with strings such as 2015-W02, meaning the second week of 2015. I would like to convert it to a date (e.g., corresponding to the Saturday of that week). It does not seem that CONVERT ...
0
votes
1answer
48 views

Counting Grouped records, using start date and end date with NULLs

Here is my issue, Source DB is Azure MSSQL - I have a table that lists all the subscriptions sold. The Relevant columns are: Account Number, Subscription Group, Purchase Date, Cancellation Date ...
0
votes
1answer
54 views

Calendar Event table - best practice setup for range queries and individual retrieval

This seems like a generic problem that should have been solved already, but I can't find anything about this. In general this question is - given a table where data is read by a date range, what is ...
2
votes
2answers
114 views

Gaps and Islands

I have a table with StartDate and EndDate. I wanted to get the missing Date and The script I have does it. But if given this data: LocationID StartDate EndDate ---------- ---------- ---------- ...
1
vote
1answer
68 views

Include all records in between two dates

I have a table with the columns ID, start_time, and end_time. I would like to extract all the records that are active during a date range. ID | start_time | end_time ----|------------|-----------...
0
votes
1answer
45 views

SQL - Date Range Broken Down by Individual Month and Year while maintaining the ID

I need help breaking down a date range, from an existing table, into the individual month and year of the date range while maintaining the Identification Number assigned to that date range. Thanks! ...
-1
votes
1answer
126 views

How to get previous date record of the most recent date record in PSQL

Table: Contract_Price Columns: Customer, Part, Location, Currency, Price, Effective_date Case: Need to get the most recent Price of Part# by the effective date. (Which I am able to get it) but also ...
1
vote
1answer
35 views

Counting working days in a month

I wrote this query that suppose to count the working days in a month but I get the following error: [42883] ERROR: operator does not exist: double precision + interval Hinweis: No operator matches ...
0
votes
2answers
44 views

Trigger: Only raise error if related record was NOT created between now and last Sunday

I have a trigger that prevents inserts into a DELETES table under certain conditions: CREATE OR REPLACE TRIGGER D202574_custom BEFORE INSERT ON D202574 FOR EACH ROW DECLARE v_upd_row NUMBER; ...
2
votes
2answers
67 views

How do I efficiently query for records that share a year?

I've got data that looks like data uid id date value 1 a 1780-01-01 10 2 a 1780-01-02 11 3 a 1780-01-03 12 4 b 1780-01-01 10 ... 999 a 1980-01-01 10 I'd like to ...
0
votes
0answers
11 views

pulling most recent results for each point based on date field

so I have tried a few methods now mostly using max(date) and not really understanding row (in) and aggregate. I have two tables in which I am doing a join that requires some field formatting to ...

1
2 3 4 5
7