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
5
votes
2answers
294 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
19 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 ...
0
votes
1answer
24 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
12 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
11 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
30 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
25 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
48 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
42 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
38 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
39 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
22 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
44 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
39 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
26 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
68 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
14 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
42 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
46 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
87 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
65 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
17 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
61 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
27 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
63 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 ...
0
votes
2answers
46 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 ...
0
votes
1answer
609 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 ...
2
votes
1answer
19 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 ...
1
vote
1answer
75 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 | ...
0
votes
1answer
28 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 ...
1
vote
3answers
48 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'...
0
votes
1answer
66 views

CASE WHEN DATE not providing correct results

When using the below query the "Result_field" returns "NO" even if the date is greater than 12/31/2020. How can I get it to show "YES" when the date is greater than 12/31/2020 and "NO" When Less Than?...
2
votes
2answers
737 views

How to add a day/night indicator to a timestamp column?

There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I only get 'DAY' as outcome. Am I not formatting the ...
0
votes
1answer
246 views

Is it possible to set timezone for a schema - Postgresql

Is it possible to set the timezone for one schema? Do not want to set Postgresql(v.12) server level. Please suggest if any ways to this.
0
votes
1answer
54 views

Getting ORA-00947: not enough values error while inserting date into table

I have created a table create table Times ( departure date not null, arrival date not null, ) and I need to insert date and time in both columns insert into Times(departure, arrival) ...
0
votes
1answer
29 views

date variable manipulation

I'm working on DB2 stored procedure, which will be called by the Korn Shell (.ksh). My DB2 version is 11.5 and it is running on AIX. I was stuck in TIMESTAMP to DATE conversion. Below is my ...
1
vote
2answers
60 views

COUNT beginning of, mid of & end per month for many years

In my last post PostgreSQL: How to get a weekly average over a year including 'gap' weeks per animal from years of data? with the fiddle http://sqlfiddle.com/#!17/0bff3/1 I got a weekly count. ...
0
votes
1answer
626 views

operator does not exist: interval < date

I want to do the following comparison : IF ( V_C_CRITERE2.DATE_FIN_SOUHAITEE - TO_DATE(V_SEUIL_ALERT) < CURRENT_DATE) THEN DATE_FIN_SOUHAITEE is a DATE. But a get this error : operator does ...
0
votes
0answers
22 views

MySQL - Check out trigger not executing

Im implementing a trigger to be executed when the end date of a booking is passed Its supposed to change a cats status within checked in from 1 to 0 when updating the table changing an ex booking ...
-2
votes
2answers
67 views

MySql : Does it worth to convert a column with type DATETIME to DATE?

At the start of a project, a DATETIME column made sense. But now, 5 years after, requierments changed the time part have never be used. My table has about 100k lines and grows with about 500 lines ...
0
votes
1answer
20 views

How to create month ranges without using a specific date in Postgresql

What's the best way to create month ranges without using specific dates? For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month ...
0
votes
1answer
40 views

postgresql cumulative counts in date range

I'm trying to get the cumulative count of rows (by group_id) between two dates that represent a time period where the row was active. I have a table like this: group_id | id | type | start_date ...
1
vote
1answer
46 views

MySQL: Unable To Create Generated Column With Expression That Uses DATE_ADD() Function

There might be a really obvious solution to my question, but I am stuck and did not manage find out what I am doing wrong, so you guys might tell me. I am not able to create a nullable stored column ...
0
votes
1answer
58 views

SQL Server column type = Date not accepting dates as varchar in insert statemant

In T-SQL, I am trying to insert values in a date table, US & French dates. When table is created with columns as DATE, the insert does not work, it errors. When I switch to VARCHAR(12) instead of ...
1
vote
1answer
263 views

PostgreSQL: How to get a weekly average over a year including 'gap' weeks per animal from years of data?

I have a lot of occurrences from different animals (see below) from many many years. I am trying to figure out how to get the average occurrence per week on a year scale per animal. I eventually want ...
1
vote
1answer
635 views

Casting int YYYYMMDD to date produces incorrect value in Squirrel-SQL

I have extracted some data from a legacy database where dates are stored as Numeric(8) values, in YYYYMMDD format. I've stored them in a staging table in that same format, and now I would like to ...

1
2 3 4 5 6