I have a table hotel_booking reference start_date numberOfDays ABC 11-08-2021 2 DEF 12-08-2021 2 GHI 13-08-2021 3 JKL 10-08-2021 2 So the first task I wanted was to get all bookings which has startDate between x and y date which is straight forward query This returns me ABC and DEF which is perfect. Now I wan…
Tag: date
ASP classic select date BETWEEN dates on SQL Server
I run the next query in my classic asp code and it run perfect. it returns results between the days “from” and “to” However,I cannot find out what is wrong with the next query: I want it to return the results of the last week that are recorded in the database. Any Ideas? The query is o…
SQL Server – Can’t change datetime to date
I have this sql which has some date format issue, problem is that I can freely change EndDate to whatever I want, but for StartDate I can’t seem to change it one bit, it always have this timestamp that I am trying to get rid of. Basically whatever I do in varchar, date or any format I just can’t c…
How to change a column’s datatype from text to datetime in mysql?
I’m trying this but it’s not working. My original data is formatted like this month/date/year (eg: 2/24/20) Answer You should create a new datetime column and populate it using STR_TO_DATE with the text date: If you’d like, you may also drop the original date column, and then rename the new …
How to return data for specific day names from date column
Imagine I have a SQL Server table as shown below, named dbo.tblNAV: How can I select the data only for Mondays and Fridays? I am a beginner in SQL Server and almost have no idea to handle this. I would appreciate your help. I am using Microsoft SQL Server Management Studio. Answer SQL Server does not make thi…
Query table with multiple “duplicates”, getting the most recent
I have a table which stores predictions from a machine learning model. This is a model that each hour (“predicted_at”) predicts a value for the next 24 hours(“predicted_for”). This means that the table have many different values for each “id” and “predicted_for”…
Display records that are not present in a table and a given date range
Let’s say I have two tables – Student and Student Attendance tables. The Student table displays basic information about the student: Student# FirstName LastName 201710 John Smith 201711 John Doe 201712 Anna Sy 201713 Chris Dy While the Student Attendance table displays the TimeIn and TimeOut of th…
Date range to row in postgres
I have a table in postgres like this: id open_date close_date 5 2006-08-04 2019-12-31 There exist 4897 days between them. I need to turn the date range to date to have one record per day. For example: id open_date close_date valid_date 5 2006-08-04 2019-12-31 2006-08-04 5 2006-08-04 2019-12-31 2006-08-05 5 20…
SQL consolidate overlapping dates based on criteria
I’m trying to merge overlapping dates between Admit and discharge dates of patients. There are a few edge cases which I couldn’t cover in the query. Input Expected Output Query I used the logic that was here But this doesn’t cover the edge case for ID 2 and 3. Also the subquery is slower whe…
Want to convert timestamp to date format in hive
want to convert this number ‘20210412070422’ to date format ‘2021-04-12’ in hive I am trying but this returns null value from_unixtime(unix_timestamp(eap_as_of_dt, ‘MM/dd/yyyy’)) Answer The best methoid is to do without unix_timestamp/from_unixtime if possible and in your c…