So "TimeDayId" is not a particularly accurate name for a DATE type column imo.  My suggestion would be to rename the column to "DayDate" or something similar

Not tested on Sql 2008 but it should work afaik.

--drop table if exists dbo.test_process_statuses;
--go
create table dbo.test_process_statuses(
  ProcessKeyId         ...

Is this really sql 2008?

Nevermind

• This reply was modified 5 years, 10 months ago by Steve Collins.

Thanks Phil I'm a big fan of your articles.  I completely agree JSON can be a key element of efficient application data access.  As far as data validation goes tho... ...

with
other_parts_cte as (
    select
      p.PartId, t.maskId, t.chemicalid
    from
      #temp t
 ...

To insert into a column of type DATE in SQL...

The 2nd item in the select list is not included in the GROUP BY clause.  Shouldn't it be?  The tables which are nested inside the STUFF function join to the...

• This reply was modified 5 years, 10 months ago by Steve Collins.

select substring(@text, len(@text)-11, 8);

You marked your own answer as the answer?  Nicely done!

declare
  @text               ...

• This reply was modified 5 years, 10 months ago by Steve Collins. Reason: now looks for reverse('_files')

Nicely done Mark Cowne.  My attempt avoids the inequality on date (which makes me nervous) but is otherwise the same.

with
t_rn_cte(person_from, person_to, kind, pctg_new, eff_date, row_num) as (
...

• This reply was modified 5 years, 10 months ago by Steve Collins.

Here's an article from SSC:

https://www.sqlservercentral.com/articles/8-ways-to-export-sql-results-to-a-text-file

If it were me I'd use BCP, the Bulk Copy Program that comes with SQL Server.

What happens if you add TOP(1)?  Will any row convert or does every row fail?

drop function if exists dbo.test_name_chopper;
go
create function dbo.test_name_chopper(
  @PREFX VARCHAR(10),
  @DELIM VARCHAR(30),
  @AD_STR VARCHAR(500))
returns table as 
return
select
  IIF(PATINDEX(@DELIM,@AD_STR collate Latin1_General_CS_AS)<=0, 
   ...

Well in my opinion just looking for a comma is risky.  Trying to comma parse the entire string multiplies the risk!  So that seems unnecessary imo.  Here's a safer way...

• This reply was modified 5 years, 10 months ago by Steve Collins.