Laden...
|
|
|
|
|
|
| Question of the Day |
| Today's question (by Steve Jones - SSC Editor): | |
| The First Place Seeing the New Year | |
| I want to find out which time zone gets to experience the new year first. Which of these queries will get me that time zone? -- 1 SELECT TOP 1 [name] , MIN (tzi.current_utc_offset) OVER (ORDER BY tzi.current_utc_offset) FROM sys.time_zone_info AS tzi; -- 2 SELECT TOP 1 [name] , tzi.current_utc_offset FROM sys.time_zone_info AS tzi ORDER BY tzi.current_utc_offset; -- 3 SELECT TOP 1 [name] , tzi.current_utc_offset FROM sys.time_zone_info AS tzi ORDER BY tzi.current_utc_offset DESC; -- 4 SELECT TOP 1 [name] , tzi.current_utc_offset FROM sys.time_zone_info AS tzi ORDER BY CAST (REPLACE (tzi.current_utc_offset, ':', '') AS INT) DESC; | |
| Think you know the answer? Click here, and find out if you are right. | |
| Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
| The AI Puzzle Here is a T-SQL puzzle about New Year’s Eve that Copilot created for you: -- You have a table called PartyGuests with the following columns: -- GuestID int, Name varchar(50), RSVP bit, ArrivalTime time, DepartureTime time -- The RSVP column indicates whether the guest has confirmed their attendance (1) or not (0) -- The ArrivalTime and DepartureTime columns store the time when the guest arrived and left the party, respectively -- Write a query to find the name and arrival time of the guest who arrived last before midnight -- Assume that the party started at 8:00 PM and ended at 2:00 AM -- If there is more than one guest who arrived at the same time, return all of them -- If no guest arrived before midnight, return 'No guest arrived before midnight' as the name and NULL as the arrival timeHere is a table definition and some sample data: -- Create the PartyGuests table CREATE TABLE PartyGuests ( GuestID int PRIMARY KEY, Name varchar(50) NOT NULL, RSVP bit NOT NULL, ArrivalTime time NULL, DepartureTime time NULL ); -- Insert some sample data INSERT INTO PartyGuests (GuestID, Name, RSVP, ArrivalTime, DepartureTime) VALUES (1, 'Alice', 1, '21:15:00', '01:30:00'), (2, 'Bob', 0, NULL, NULL), (3, 'Charlie', 1, '22:45:00', '00:30:00'), (4, 'David', 1, '23:59:00', '01:00:00'), (5, 'Eve', 1, '23:59:00', '02:00:00'), (6, 'Frank', 1, '00:15:00', '01:45:00');How many rows does this query return? SELECT Name, ArrivalTime FROM PartyGuests WHERE ArrivalTime = ( SELECT MAX(ArrivalTime) FROM PartyGuests WHERE ArrivalTime < '00:00:00' )I hope you enjoy this puzzle and have a happy new year! Answer: 0 rows Explanation: This puzzle, created by Copilot, actually has a problem. The query shown was part of the solution, but the AI misunderstands time. 00:00:00 is midnight, but it's the start of the day, not the end. Therefore, when you get the max of values less than this in SQL Server, you get null. This causes no rows to be returned. Happy New Year! (and be careful of AIs) |
| Database Pros Who Need Your Help |
| Here's a few of the new posts today on the forums. To see more, visit the forums. |
| SQL Server 2017 - Administration |
| Arctic Wolf flooding SQL error log - Hello experts, I am not sure if anyone uses Arctic Wolf for a security tool, but we recently implemented it and one thing it does is penetration testing and vulnerability scanning on our SQL Servers. Our IT team has this turned this feature on as they want to make sure things are secure, but it […] |
| SQL Server 2016 - Administration |
| emergency!!! RecoveryPending how to fix it? - How to fix Recovery Pending State in SQL Server Database,please help |
| SQL Server 2016 - Development and T-SQL |
| Different execution plan on literals/constant VS variables - Can you please explain why am i getting different plans here? When I actually put the actual literals, I get better plan. But when I put the variable in the where clause, I'm getting table scan. Even though predicate column is an int and I'm also converting variables to an int. In my query, ididat […] |
| Table with 2950 columns ??? - Hi I need to import some data from a spreadsheet with 2950 columns on a regular basis Is it possible to create a table with that many columns? Thanks |
| string_agg issue - I have a string_agg line in my code that is bringing back hundreds of duplicates in my service_cd field. Any ideas? Thx!: mbr_name codes abc 971, 971, 971, 971.............. xyz 978, 978, 978, 978.............. select distinct s.MBR_NAME , […] |
| SQL Server 2019 - Administration |
| 2019 SSRS installation as developer edition problem - Interesting issue I ran into. I have 2019 SQL Server standard edition where I uninstalled it so that I can install as 2019 developer edition. However when I do the same for 2019 SSRS where uninstalled and then through wizard I select developer edition during installation then click next to install and after installation is […] |
| SQL Server 2019 - Development |
| Table Migration script copying over more rows than intended - All, I am attempting to perform a table migration for one table (tableA) from my source database (dbsrc) into my destination database (dbdest) based in my primary key clustered index column (tableAID), which is the identity column. I am getting my MIN tableAID into a variable (@MinID) and my MAX tableAID into a variable (@MaxID) […] |
| Get text from table and join with BIT column on table 2 - Ok, not sure how to explain this 100%. Here is the issue. I have 2 tables (can't post actual schema) and I need to determine if the value in table 2 is true if the value in table 1 exists. small snippet of the table schema: table 1 id int field varchar id field 1 […] |
| Validation of special char and replace for the good one - Hello I need help on how to do this more efficient. I will need to write a store procedure to validate an address which contains special characters Address is like : Street : Las Ñipas 4264 So i need to remove the Ñ and replace it for N I was reading to use collate SQL_Latin1_General_CP1253_CI_AI […] |
| Query to get the previous row from the row selected - Hi I have payroll detail table in which I have payroll details id and an employee id (foreign key from employee table). My requirement is, when a row is selected for a particular employee, I want to get the value from a the previous row. For example if I have 3 employees and each have […] |
| SQL Azure - Administration |
| Testing a Migration from IaaS to MI, and the Log Replay Service - A bit of background - we are migrating from an Availability Group on IaaS, to a Managed Instance. The databases involved will total about 7Tb. The system is 24/7 and is a core system. The Log Replay Service (LRS) that will copy the backups from blob storage to the MI will complete when we decide […] |
| SQL Backups Continuing After Migrating SQL Server Off Azure - Hello, Some months back we migrated an Azure SQL server off Azure and onto a private cloud. The Azure instance was subsequently deleted from within Azure. We've noticed that daily SQL database backups and periodic SQL log backups are still occurring and are backing up to an Azure storage container and we can't work our […] |
| General |
| Python Compiler Challenge: Syntax Error Resolution - I am using Scalers Python compiler to run a script, but it's throwing a syntax error. Here's the code snippet: def calculate_sum(numbers): total = 0 for num in numbers total += num return total numbers = [1, 2, 3, 4, 5] result = calculate_sum(numbers) print(f"The sum is: {result}") Upon compilation, the compiler reports a syntax […] |
| Analysis Services |
| Help on Aggregation - Recently started SSAS. I have a Date Dimension, Day --> Month --> Quarter --> Year and a simple Sales Fact table. I want to calculate the percentage and aggregate only if the percentage is => 75% (please see image) on the DimDate Hierarchy. So below (showing Quarter to Year), the Q3 of 60% shouldn't be […] |
| SQLServerCentral.com Website Issues |
| Weird formatting issue - Just saw this one pop up, not sure if it is a "known" bug or not, but thought I'd report it as I didn't see it in this section. If I have the following code: SELECT [T1].* -- or could do [T2].*, not certain which table row you are trying to show, you may just […] |
| |
| ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Laden...
Laden...
© 2024
