Tag: tsql

SQL: Display “01 Mar 2017” instead of “2017-03-01” ISSUE WITH GROUP

I have this code below that works fine, but I would like to change the shape of dates to look like “01 Mar 2017” instead of “2017-03-01” SELECT * FROM ( SELECT [MessageType].[Name], CASE WHEN ( Format([OccuredAtUtc], ‘yyyy-MM-dd’) LIKE ‘2017-03%’ ) THEN Format([OccuredAtUtc], ‘yyyy-MM-dd’) ELSE NULL END AS [Time], COUNT(*) AS [Count] FROM @Table INNER […]

Convert VARCHAR in format YYMMDD to YYYYMMDD and ignore invalid date formats

I have a table with a VARCHAR field called ArrivalDate in format yymmdd (such as 170202). I am writing a query which converts it to yyyymmdd so it should become 20170202. However my problem is that I need to cater for the case when inappropriate data is entered into the field, and my query needs […]

Updating records depending on the intersection of dates

I have a table that looks like this: ID | Start | End | Flag1 | Flag2 ——————————————– 1 | 1900-01-01 | 1900-01-01 | 0 | 1 1 | 1900-01-01 | 2000-01-01 | 1 | 0 2 | 2010-01-01 | 2020-01-01 | 0 | 0 For any date range of an ID, flags 1 and […]

Get the groups items after a GROUP BY in SQL Server

I have a table that has these columns: UserID1, UserID2, ProductID, PurchaseDate The following query run in a purchases table and return couple of users that have more than a number of interactions between them, regardless the order in the last 31 days: DECLARE @threshold AS INT DECLARE @days AS INT SET @threshold = 10 […]

SQL, from , seperated rows to colums

Create table test123 ( SeqId int, string1 varchar(100), string2 varchar(100) ) insert into test123(SeqId,string1,string2)values (10,’a,b,c’,’x,y,z’), (20,’d,e,f’,’p,q,r’) output SeqId RowNum String1 String2 10 1 a x 10 2 b y 10 3 c z 20 1 d p 20 2 e q 20 3 f r

How to get no of days from MON-YYYY data

Please let me know how can i get number of days in a month when I give date in MMM-YYYY format. Eg: JAN-2017 = 31 Any helps appreciated.. Thanks

How do I subtract inside of an IN clause in SQL?

I inherited a ASP application that I’m moving to a PHP codebase. One query that I’m struggling with is as follows select * from ( select TERM_REPORTING, LEVEL_GROUPING_DESC, New_primary_college_Desc, ID_count from Enrollment ) a pivot(sum(ID_count) for TERM_REPORTING in ( [” & 201609 – 400 & “], [” & 201609 – 300 & “], [” & […]

How to avoid UPDATE statement locking out the entire table when updating large number of records

I am fairly new to locks and hints. I have a table with very frequent SELECT and INSERT operations. The table has 11 million records. I have added a new column to it and I need to copy over the data from an existing column in the same table to the new column. I am […]

Merge script between 2 tables and not working

I have a code that i was still wondering why it should not work, I want to merge these two tables and look at the code below and tables with error generated. Please the tables are just extracted, and have more rows than pasted below. Just sample MERGE INTO [server1].[DATABASE].[dbo].[MD_ToolsMaintDate] WITH (HOLDLOCK) AS TARGET USING […]

Filtering consecutive dates in SQL Server

I would like to filter consecutive dates from my holiday table if any of the consecutive date is yesterday. Below is my code to get consecutive dates with row numbers. SELECT RW = ROW_NUMBER() OVER( PARTITION BY GRP ORDER BY HolidayDate), HolidayDate FROM (SELECT HolidayDate, DATEDIFF(Day, ‘1900-01-01’, HolidayDate) – ROW_NUMBER() OVER (ORDER BY HolidayDate) AS […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.