The function could be included in the SELECT list

select lcd.LoggerDownloadTableName,
       smalldatetimefromparts(substring(lcd.LoggerDownloadTableName, 18, 4),
        ...

You could try SMALLDATETIMEFROMPARTS

select v.input, 
       smalldatetimefromparts(substring(v.input, 18, 4),
            ...

The least friction way imo seems to be to split each character using fnTally and convert to ASCII values.  Then re-aggregate the string.  Something like this (with extra non-[a-z][0-9]) characters...

You could try something like this

select ph.PolId, 
       case when isnull(pa_est.est_count, 0)>0 
        ...

Sure, the function is described in this really nice article.   This is the code I use

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC:...

This query is really close but maybe there's an inconsistency issue with the 'tab1_result' query?

with tab1 as
(
    select 101 as id, 'abc' as area,'2021-01-07'...

In the code below 'start_cte' creates a column called 'start_ind' which is equal to 1 when the AVAILABLE column has changed from 0 to 1 else it is 0.  To...

• This reply was modified 5 years, 3 months ago by Steve Collins. Reason: Changed stop_ind to start_ind to make clearer

Because they're different tools for different jobs.  JSON or XML are supposed to be versatile(unlike older mainframe type hierarchies) and capable of being transmitted and consumed by a broad...

The issue isn't so much that SQL server can't parse and format json it's that the data is represented in fundamentally different ways.  And what your translation layer is...

Fighting the tools is futile in most cases...

It seems the CASE conditions could be simplified because it's not necessary to test the CHARINDEX prior to REPLACE.  If the search characters are not present in the string then...

With the 2 SQL functions and 1 procedure above in place, the following steps should results in a functional Forum API with 32 .NET Core 3.1 API endpoints wired up...

.

.

.