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
17 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
670 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
36 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
0answers
31 views

MS SQL Server 2012 - How To Find Last Executed Queries

I have 120+ databases and I want to find last executed queries (actually I want to find last executed queries and last executed select queries) in last 10 days. I have below scripts for this job: 1-) ...
0
votes
1answer
22 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
22 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
57 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
24 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
48 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
14 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
23 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
19 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
44 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 ...
0
votes
0answers
11 views

Designing operational hours schema [duplicate]

I need to describe a cyclical calendar of working hours for physical locations (e.g. restaurants). From user's perspective, the user would see: Monday 8:00 to 11:00, 12:00 to 02:00 (02:00 here is ...
1
vote
1answer
33 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
183 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 ...
0
votes
2answers
36 views

How to select Overdue Rows with Date Frequencies?

+------------------------+--------+ | Invoice_id | due_date | amount | +-------------+----------+--------+ | 20 |2020-01-18| 1250 | +-------------+----------+--------+ | 21 |2020-...
-2
votes
1answer
19 views

Can't manage to work with comparing days

In a table with persons and their birthdays, the ones whose birthday is in less than 7 days need to be displayed. I found some answers on the Internet but they all are at least 5 years ago with ...
0
votes
1answer
273 views

MySQL CURDATE() and CURRENT_TIMESTAMP return different value?

I am facing a little confusion because of CURRENT_DATE value and CURRENT_TIMESTAMP value shows different date on MySQL. Scenario : 1) User calls API from Lumen backend to insert data on a table. One ...
0
votes
1answer
55 views

Most efficient way to query a date range in Postgresql

I have a table with a timestamp with tz column. I want to perform a count / group by query for rows within a certain date range: select count(1), url from app_pageview where viewed_at between '2019-...
0
votes
2answers
106 views

MySQL Coalesce date missing results, why?

Running MySQL 8.0.16, settings left at defaults as far as I know. I have a table, let's call it the_table, with columns id: bigint primary key auto_increment the_time: datetime I have a record ...
0
votes
3answers
47 views

Retrieving users by their age efficiently

I have a PostgreSQL database of users and their birthdays and want to get all users in a specific age range: SELECT * FROM users WHERE age(birthday) >= 21 AND age(birthday) <= 30; How can I ...
-2
votes
1answer
93 views

Working with NULL Date Fields [closed]

I have a derived VARCHAR date field that I need to use for deriving another field, based off of whether or not the date field has a value. The derived date field is called "AmortDate" and I'm ...
1
vote
1answer
26 views

MySQL wrong use of index when a date interval is big

I have a query that have this statement: SELECT DISTINCT C.id_produto FROM movdiario C INNER JOIN PRODUTO P ON C.ID_PRODUTO = P.CODITEM INNER JOIN estoques B ON B.id_produto=C.id_produto ...
0
votes
1answer
51 views

How do I form an SQL query to show entire column of dates with modified values?

Assume that I have a table Purchases with column named PurchaseDate and with data format of 'YYYY-MM-DD HH:MI:SS'. Suppose that I want to see the entire column of dates with the days increasing by 5. ...
0
votes
1answer
35 views

Locating all records relevant for a specific date

I have a MySQL InnoDB table with about 1GB of records and growing. each record in this table has, among other fields two date fields : AddedDateRecord - The date the record was added (datetime). ...
-2
votes
1answer
460 views

Calculate nth business day of the current month

Looking for a simple inline statement (MSSQL) that can return the current workday number of the month (number of days elapsed minus Saturday and Sunday) E.g. NOV 11 2019 would be the 7th working day.
0
votes
2answers
49 views

How to add time from a Date value to another without a varchar conversion?

Let's say I have 2 variables in a Stored Procedure, both are Dates Foo contains '13/10/2019 00:00:00' Bar contains '01/10/1914 16:33:11' I want to add Bar's time to Foo's date, so that I get a ...
0
votes
1answer
18 views

How to ensure all records of a header are under the same fiscal year?

Say I have the following schema: BUDGET_YEAR_TARGET INTEGER ID BUDGET_DATE_TARGET INTEGER ID INTEGER BUDGET_YEAR_TARGET_ID--Foreign key DATE DATE How can I ensure that any records ...
1
vote
2answers
62 views

MYSQL: How to optimize a date sorted table with a triple primary key index

I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began ...
0
votes
2answers
48 views

Fill in missing dates for a vacation schedule

Have an issue that has been driving me nuts for the past week and can't seem to get my head around it. I have a stored procedure that grabs a bunch of vacation requests from our scheduling system. I ...
0
votes
1answer
135 views

Performance MySQL query with for loop

In this method, first I have to get sundays dates between two dates, in this case about 1 year. Then I go through the dates in a for loop and set them to the query. I use prepared statements to make ...
0
votes
1answer
493 views

How to alter database hour and date in Oracle?

How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.
0
votes
0answers
27 views

Create range from one date

So, I have a specific problem, that I can't solve without a function. Here's a simple code I wrote which if can be made to work, will solve my bigger issue. with dates as ( select year_start, day ...
1
vote
2answers
180 views

Equivalent of Oracle RR date format in SQL Server

Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server. The Oracle export has date formats like this: to_date(...
0
votes
1answer
45 views

Best way to put date range in a table in mySQL

I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in ...
1
vote
2answers
69 views

Setting a date dynamically

I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it. The query is doing a count of the number of rows with an entered on date >= 1st August ...
0
votes
1answer
238 views

Sharding a MongoDB instance by a spatial key and a date

This may be more an advice than anything else, but I'm designing an app that stores in a table objects that have a 2dsphere index and they also have a date (+time), both of them being part of queries ...
0
votes
1answer
41 views

Column with Default value as Sum of TIMESTAMP

I have a table which has 2 columns ( JobDate , RecordTime) -------------------------------------- JobDate | RecordTime | SumCol -------------------------------------- 2019-07-20 | 2019-07-...
0
votes
0answers
151 views

How to group by a field in a certain time range in Cassandra

I have a big Cassandra table with around 15 fields, and I wanted to create many materialized views to support all of my queries. The Primary Key on the base table is (CompanyName, ctime) where ctime ...
0
votes
0answers
88 views

Redshift/Athena: Most efficient way to cast timestamps of varying precision to date in query?

I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the ...
3
votes
1answer
183 views

Fill missing dates within groups

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
1
vote
1answer
37 views

MariaDB CONNECT engine BIN table - file record layout, storage of a date data type, padding

In mariadb-10.4.5, running on Windows 10, I created this table (in the local database): `CREATE TABLE IF NOT EXISTS `testbalNoFieldFormat` ( `fig` int(4) NOT NULL, `name` char(10) NOT NULL, `...
-1
votes
1answer
24 views

Why am I not able to filter rows based on date? [closed]

I am trying to apply a filter on dates which is a string type but I am not able to get the desired results. for eg, select * from table_1 where date between '01-01-2016' and '31-12-2016'; The ...
0
votes
1answer
132 views

Unable to use today's date as a criteria in a query

Ok, been a good decade since I've worked with Access and SQL, so a little forgiveness please. Short version - when I add the query criteria to only display rows which match today's date, the query ...
1
vote
1answer
69 views

Convert Date and Filter by Year

I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field. For example, in my table there is a "Posting Date" field that is in the '20180401' ...
0
votes
2answers
2k views

Is it possible to write a SELECT statement to obtain all dates between 2 date columns?

My question is related to these 2 questions: https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates/27319120 https://stackoverflow.com/questions/1378593/get-a-list-of-...
0
votes
1answer
314 views

How to convert timestamp with time zone to numeric (unix) in PostgreSQL?

I have timestamps with time zone (timestamptz) in my table. Can I convert them to numeric or int in unix timestamp format? I found this question, but it shows only how to convert timestamp without ...
0
votes
0answers
39 views

MySQL date_format German date value not change to date type

I have a column that is varchar and the value of the the data is a date (i.e. 31.05.2019) I'm trying to convert the varchar field to a date field using the following function: date_format(...

1
2 3 4 5 6