I have a database in which, I have a issue_date column, and forecast_date column, I am selecting the maximum date from the database, but I want to fetch/query/extract the N th previous day from maximum available date like (maximum date – 1 / 2 or n number of days). & As the max date is custom, so ca…
Tag: date
How to round timestamp to nearest day with postgresql
How can I round a timestamp to the nearest day in postgresql. I have tried using function date_trunc(‘day’, d_date::timestamp) but I get that is always staying in the same day. Example 1: Example 2: (This one works fine) Answer You could add 12 hours to the timestamp and then do the truncation tha…
My query to retrieve events between 2 search dates does not return any results, why?
Context I have modelized a calendar with a table. The entries are the calendar’s events: id ; event_start_date ; event_end_date. One entry is: 11 ; 2021-01-28 15:15:37 ; 2022-01-11 15:15:37 The script I’ve written accepts a search interval of dates as entries and should return all the events which…
How to read from the two same dates without input the time
I have a query like below. When I submit a date like the following patients.created_at BETWEEN ‘2018-05-22’ AND ‘2018-05-22’ it doesn’t return anything but if I enter BETWEEN ‘2018-05-22’ patients.created_at ‘AND’ 2018-05-23′ it returns a value. I th…
Handling of generate_series() in queries with date or timestamp with / without time zone
I have a query to generate a report based on a date series that is grouped by date and employee_id. The date should be based on a particular time zone, in this case ‘Asia/Kuala_Lumpur’. But this can change depending on where the user’s time zone is. Definition and sample data for table atten…
Check for overlapped dates on any row of a table Oracle SQL
I have the following schema: CREATE TABLE EPOCA ( ID INT CONSTRAINT PK_EPOCA PRIMARY KEY, NOME VARCHAR(250), DATA_INI DATE CONSTRAINT NN_EPOCA_DATA_INI NOT NULL, …
Create date table of every second between two dates
I need to create a table of every second between two input dates eg between 01/01/2015 00:00:01 and 05/01/2016 00:00:00 So a 3m row expected output that looks like this: Does this need a CLR? …
How to group by month and year?
I have a table PURCHASE with a date column PURCHASEDATE which is in DATE format. I’m trying to get the purchases grouped by month and year. To do so, I try with: I have also tryied with GROUP BY EXTRACT(MONTH FROM PURCHASEDATE), EXTRACT(YEAR FROM PURCHASEDATE) but neither worked. I’m rusty with SQ…
Big Query – Calculate start and end date back to back
I have a problem, which I need some advise, I am required to calculate the number of leave calendar days taken back-to-back on big query. (For eg. 2 leave records taken on 07-01-2020 to 10-01-2020 and 13-01-2020 to 15-01-2020, should return 07-01-2020 to 15-01-2020) However, there are certain weeks, where lea…
SQL Server find rows approaching a service day and month
I have a table which lists equipment with an installation date. These items need to be serviced annually or every five years. So the rows have a service interval of 1 or 5. Something like: What I need to do is return a list of equipment two months before they are due for service For simplicity Let’s ass…