I have two date fields in my Netezza table and I wish to find the number of days between them. The original table I pull these two fields from has them recorded as DATE. I wish to subtract SECOND_DATE from FIRST_DATE and wish to include the magnitude i.e. negative days in this example There are -158 days between SECOND_DATE and
Tag: date-arithmetic
SQL select records with current month
I have a field name textbook date field name NewMonth The data like this My goal to select the records with current month (2020-04-XX) My query and not working. Can some one correct my query. Thank you I think -1 for current month, -2 for last 2 months , -3 for last 3 months andso on Answer My goal to
MySQL 5: How to find the peak of customers during working time
I have table in MySQL with times spent by customers and I need to find the most busy 30 mins. Expected result is something like multiplerows with time and number of customers: I can easily make 5 sql queries and get the result (I made some view in similar problem https://stackoverflow.com/a/59478411/11078894 ), but I do not know how to get
Get all days in a month excluding weekends postgresql
I want to write a query sql for postgresql that can basically return me all days of a month excluding weekends. For example (For 11/2019) : First Week: 11/1 Second Week : 11/4 -> 11/8 Third Week : 11/11 -> 11/15 Fouth Week : 11/18 -> 11/22 Fifth Week : 11/25 -> 11/29 I can’t find any postgresql request that
Create a calendar database table like this
I would like to create a calendar SQL table like this one but for some years. (I’m using mysql 5.7.28) Date is DD-MM-YYYY Is it possible? Answer If you are running MySQL 8.0, you can use a recursive query: The recursive cte generates a list of datetimes between the given boundaries (here, that’s year 2019), with a 1 hour increment.
Get Max And Min dates for consecutive values in T-SQL
I have a log table like below and want to simplfy it by getting min start date and max end date for consecutive Status values for each Id. I tried many window function combinations but no luck. This is what I have: This is what want to see: Answer This is a typical gaps-and-islands problem. You want to aggregate groups
Month Name and Last date of each month for previous 3 months in sql
I’m analyzing customer transactions for the previous 3 months, excluding the current month, I would like to get the month names of the previous 3 months and the last date of each of the 3 months. —output —expected Answer You can use SQL Server EOMONTH() function to compute the last day of a month. Consider:
In SQL how to calculate days in a year based on a start date and the number of days lapsed
What would be the SQL to calculate the number of days in each year if I had a start date and the number of days that have lapsed? For example, the date (ymd) 2013-01-01 and the days lapsed is 1000. …
how to find Age from DOB in Athena?
Here is my query to find out age from the DOB column : I am getting following error: SYNTAX_ERROR: line 4:47: ‘-‘ cannot be applied to varchar, varchar Appreciate your help! Answer You should be able to solve this using function DATE_DIFF(). From the documentation: date_diff(unit, timestamp1, timestamp2) → bigint Returns timestamp2 – timestamp1 expressed in terms of unit. Try:
Can you define a custom “week” in PostgreSQL?
To extract the week of a given year we can use: However, I am trying to group weeks together in a bit of an odd format. My start of a week would begin on Mondays at 4am and would conclude the following Monday at 3:59:59am. Ideally, I would like to create a query that provides a start and end date,