Tag: sql

Handling of generate_series() in queries with date or timestamp with / without time zone

date generate-series postgresql sql timezone

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

date insert oracle sql triggers

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, …

Calculate age from date of birth with formatting

plsql sql

I would like to calculate the age from the date of birth stored as a string using the format in the comments in the code. Here’s my code: The result I get is the following: I guess there’s something wrong with the year but I can’t figure it out! Answer The error is in this line: You should n…

Can you use ORDER BY (column) LIMIT with a secondary column?

count left-join mysql sql sql-order-by

I want to find out the Top 10 rented movies in the sakila db. It must be sorted DESC after total_rentals and secondary after film title. My solution looks like this and works so far without the film …

Show sales per employee (staff) and year in sakila db?

mysql sql

I’m kind of stuck on an excercise concerning the sakila db. I want to show sales per employee (staff) and year in mySQL and I’m failing at using the staff_id’s correctly. This is all I have so far: …

Create temporary table to display missing months from query

left-join mysql sql

I am trying to create a simple MySQL query to get the sales data for each month of the year, grouped by monthname and SalesName. CREATE TEMPORARY TABLE Cal (monthid INT, monthname VARCHAR(50)); INSERT …

Date Format Convert

sql sql-server tsql

I am dealing with sql DB where the date is recorded in this format ‘2020-348T08:55’. I need to convert this to datetime. Please guide if there is possible way to do that? Answer With string and date/time functions extract the year and days to create a DATETIME without the time and finally add the …