Skip to main content

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
4 votes
1 answer
560 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
107 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
67 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
886 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,463
0 votes
1 answer
217 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 ...
User1974's user avatar
  • 1,477
1 vote
1 answer
5k views

How to calculate the number of days between two dates?

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 there between that day and the current date. I tried doing: SELECT ...
Santi's user avatar
  • 11
1 vote
3 answers
266 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 ...
WIIcky's user avatar
  • 11
0 votes
2 answers
761 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 ...
JOSH W's user avatar
  • 27
3 votes
1 answer
519 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....
A.Motahari's user avatar
0 votes
2 answers
775 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 | |----...
ᴍᴇʜᴏᴠ's user avatar
7 votes
1 answer
11k 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 ...
baggiponte's user avatar
0 votes
2 answers
1k 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 ...
Reena Hirani's user avatar
0 votes
2 answers
24 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 ...
mcv110's 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
Karsten Jaquet's user avatar
2 votes
1 answer
2k 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 ...
AndreKR's user avatar
  • 587
0 votes
2 answers
6k 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 ...
john doe's user avatar
  • 115
-1 votes
1 answer
2k 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-...
Lame Fanello's user avatar
5 votes
2 answers
2k 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 ...
Lion Smith's user avatar
1 vote
1 answer
324 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 ...
rubengavidia0x's user avatar
1 vote
1 answer
4k 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 ...
HappyCoder123's user avatar
1 vote
1 answer
60 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 ...
Closet_Monsters's user avatar
1 vote
1 answer
431 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 2021/01/24 6 4 2021/01/25 55 Result should be: tracking_id date_action ...
Pushpa 01's user avatar
-1 votes
1 answer
1k 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. ...
Elio Fernandes's user avatar
0 votes
1 answer
8k 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 ----->...
Nader Gharibian Fard's user avatar
2 votes
2 answers
255 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 ...
Toby Allen's user avatar
1 vote
2 answers
4k 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' ...
we need a Mat. Stat.'s user avatar
0 votes
1 answer
681 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: #...
IMB's user avatar
  • 169
0 votes
1 answer
1k 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 ...
Brynster's user avatar
0 votes
2 answers
714 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 ...
HappyCoder123's user avatar
1 vote
0 answers
10 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....
lolveley's user avatar
  • 141
0 votes
1 answer
20 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 ...
Bobbie D's user avatar
  • 115
0 votes
2 answers
157 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 ...
pauldanc's user avatar
-3 votes
1 answer
72 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 ...
PatrickClark's user avatar
1 vote
1 answer
2k 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 ...
learning's user avatar
-1 votes
2 answers
129 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 ...
Andrea Dapor's user avatar
0 votes
1 answer
409 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 ...
TheDemonLord's user avatar
0 votes
1 answer
183 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 ...
richard's user avatar
  • 111
2 votes
2 answers
173 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 ---------- ---------- ---------- ...
Jeni's user avatar
  • 23
0 votes
1 answer
127 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 ----|------------|-----------...
friday-json's user avatar
0 votes
1 answer
945 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! ...
JOSH W's user avatar
  • 27
-1 votes
1 answer
1k 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 ...
Ali's user avatar
  • 1
1 vote
1 answer
1k 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 ...
Aww's user avatar
  • 11
0 votes
2 answers
549 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; ...
User1974's user avatar
  • 1,477
2 votes
2 answers
82 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 ...
John Baber-Lucero's user avatar
0 votes
2 answers
71 views

Can somebody please tell what is the difference between these two date comparisons?

So I have 2 queries as follows: f.DATE >= (GETDATE() - 3) f.DATE >= CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME) I think they are the same but the second one gives me more rows ...
mameo's user avatar
  • 1
0 votes
1 answer
4k views

Split date range (timestamp) into equal parts by Month- SQL Server

Experts, Have a question regarding splitting a date range into equal parts by months including the time part example - fromdate - 06/29/2020 09:00:00 and todate - 06/29/2021 09:00:00 Want to split ...
Tech_Enthu's user avatar
2 votes
2 answers
593 views

Is there some way to store EITHER a specific day (date) OR a year in the same column?

I have a column called "date or year of birth". The reason it's not called "date of birth" is that, in some cases, the exact day is not known -- only the year, or only the year and ...
Ellwood Gansert's user avatar
1 vote
1 answer
784 views

SQL Server - Get the Date Ranges

I have a table with start date and end date. For example, PlaceID | StartDate | EndDate 1 | 2020-01-01 | 2020-01-04 1 | 2020-01-10 | 2020-01-15 2 | 2020-01-03 | 2020-01-06 2 | ...
user avatar
0 votes
1 answer
1k views

Count days in date range but only for a specific year within the date range

I have several date spans in a SQL Server table. I am trying to count the number of days in the date span for a given year in the date span. For all my examples I am trying count the numbers of days ...
Juan Velez's user avatar
  • 3,253
1 vote
3 answers
3k views

I want to get all the weeks of a month with the start and end dates of those weeks without creating a table

My SQL is: SELECT date_trunc('week', '2020-06-01'::timestamp)::date ||'-'|| (date_trunc('week', '2020-06-01'::timestamp)+ '6 days'::interval)::date; However, using this code it shows only one week'...
Aslam's user avatar
  • 11

1
2
3 4 5
7