If you are unable to change the structure of your DB, you could use a splitter function.  In SQL 2016, you have a built-in splitter function which I believe should...

I have a situation whereas I need to include a column that is nonexistent in the table(s).  The primary table has a CreatedDate and an UpdatedDate field.  I need...

How nice it is to see this again - a bunch of folks working the hell out of a problem to come up with their solutions, each one faster...

Object names cannot be passed into sp_execute_sql.

On another note, there is no need to use nvarchar(max) for your object types.

  @table     ...

So, although my prev code produced the correct results, it was clunky at best.

Here is a cleaned up version of the code

CREATE FUNCTION dbo.MonthStartEnd( @DateInMonth date )
RETURNS...

I have modified the proc to do a check and cater for the date that falls into the next month

CREATE FUNCTION dbo.MonthStartEnd2( @DateInMonth date )
RETURNS TABLE WITH...

For a single value, you can still call the function twice

DECLARE @TheDate AS date = '2019-01-28';

SELECT MonthStart  = CASE WHEN @TheDate > mse.MonthEnd THEN mse2.MonthStart ELSE...

• This reply was modified 6 years, 5 months ago by DesNorton.

Thanks Des, That Table is just static showing  the Start and End Months. It will not be available in Production. Using your function still gives me incorrect dates when...

Start with a iTVF that will calculate the dates for the month

CREATE FUNCTION dbo.MonthStartEnd( @DateInMonth date )
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
  WITH cteBaseValues AS (
 ...

• This reply was modified 6 years, 5 months ago by DesNorton.
• This reply was modified 6 years, 5 months ago by DesNorton.

So, you now get a number from the Sequence Object

DECLARE @PolicyNumb AS INT
Set @PolicyNumb = NEXT VALUE FOR PolNo

but you do not use it

Insert...

You could always use a SEQUENCE object to generate an ever increasing number

You would then use NEXT VALUE FOR in order to get the next number.

Since you appear to be using SQL 2016, you can also look at the new STRING_SPLIT function

Based on the sample data, this should get you what you want

WITH cteStarts as (
  SELECT StartID = LogID
  FROM #Logs
  WHERE LogMessage =...

On a side note, if you have any control over the structure, you would be better off storing the date as a DATE or DATETIME field.  Then it can be...

Hi, I have found things online about this but nothing is working. I have a field that is a Varchar(20), which holds a Date in it (like this 05/04/2019)....