T-SQL stored proc syntax question

I am writing a stored proc and I can't get it right in one part, wehre I have to "convert" a date stored as an integer (with the format yyyymmdd, i.e. 20180126 for example for today, to a smalldatetime local variable.

Here the code for that part:

DECLARE @LastWorkingDay int
DECLARE @dLWD smalldatetime
DECLARE @sLWD char(8)
DECLARE

    -- Get the last working day from the DB table
      SELECT @LastWorkingDay = LastWorkingDay FROM [dbo].[V_Dates];
    -- Convert it to a string
      SET @sLWD = CAST(@LastWorkingDay as char(8))
    -- Chop the bits up to convert it to YYYY-MM-DD format and cast it to smalldattime
      SET @dLWD = CAST(Left(@sLWD, 4) + '-' + SUBSTRING(@sLWD, 5, 2) + '-' + Right(@sLWD, 2) as smalldatetime)

One of the SET line gives an "Incorrect syntax near the keyword 'SET'" error. I say one because I don't quite know how SQL Server is counting the lines in a stored proc.

Can you tell me what is wrong in my code ?

Also, can you tell me how I can see the progress of the steps in a stored proc, is there some kind of an equivalent to Console.WriteLine in VB or C#, seemingly I could also get the status back but I also don't know how to do that.

Thanks a lot
Bernard
LVL 1
bthouinIT Analyst and developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @sLWD char(8)
DECLARE    ------------------------------------------ remove this line
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<wild idea>  Make sure your expression evaluates to a value that can be converted to a date, then assign...
IF ISDATE(CAST(Left(@sLWD, 4) + '-' + SUBSTRING(@sLWD, 5, 2) + '-' + Right(@sLWD, 2))
   begin 
    SET @dLWD = CAST(Left(@sLWD, 4) + '-' + SUBSTRING(@sLWD, 5, 2) + '-' + Right(@sLWD, 2) as smalldatetime)
   end
ELSE
  begin
  SELECT 'Ruh roh.  This cant be converted to a date --> ' +  Left(@sLWD, 4) + '-' + SUBSTRING(@sLWD, 5, 2) + '-' + Right(@sLWD, 2)
  end

Open in new window


>SELECT @LastWorkingDay = LastWorkingDay FROM [dbo].[V_Dates];
This also implies there is only one row in your v_dates view.
0
bthouinIT Analyst and developerAuthor Commented:
oh sh...

Yes, at least it runs... thanks, I'm an idiot ...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bthouinIT Analyst and developerAuthor Commented:
Hi Jim

>>This also implies there is only one row in your v_dates view<<
Yes, it's the case.

But I'm not confident about the date I pass then to a further select statement. How can I visualize my @dLWD  variable when the sp runs ?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Far better to be an idiot with us, who are suckers for points and don't know any of your co-workers, then to be an idiot with them..

>How can I visualize my @dLWD  variable when the sp runs ?
Not sure what this means.  You can always SELECT blah blah blah WHERE NOT ISDATE(your expression) to flush these out, then figure out a way to gracefully handle them so that they are all valid dates.

Or simply don't include them in your code's downstream stuff.
0
ste5anSenior DeveloperCommented:
When it is stored as INT, then why wasting time with string manipulations?? It's simple math:

DECLARE @DateAsInt INT = 20181126;

SELECT @DateAsInt / 10000 ,
       @DateAsInt % 10000 / 100 ,
       @DateAsInt % 100 ,
       DATEFROMPARTS(@DateAsInt / 10000, @DateAsInt % 10000 / 100, @DateAsInt % 100);

Open in new window


In a query:

DECLARE @Sample TABLE
    (
        ID INT IDENTITY ,
        DateAsInt INT
    );

INSERT INTO @Sample ( DateAsInt )
VALUES ( 20181126 );

SELECT S.DateAsInt / 10000 ,
       S.DateAsInt % 10000 / 100 ,
       S.DateAsInt % 100 ,
       DATEFROMPARTS(S.DateAsInt / 10000, S.DateAsInt % 10000 / 100, S.DateAsInt % 100)
FROM   @Sample S;

Open in new window

1
Scott PletcherSenior DBACommented:
Just CAST it to char/varchar, you don't need, nor want, to do anything else.  'YYYYMMDD' is always 100% unambiguous in SQL Server, but 'YYYY-MM-DD' can be misinterpreted, depending on SQL settings.

DECLARE @LastWorkingDay int
DECLARE @dLWD smalldatetime

SET @LastWorkingDay = 20180126
/* YYYYMMDD */
SELECT @dLWD = CAST(@LastWorkingDay AS varchar(8))
SELECT @dLWD

/* YYYY-MM-DD */
SELECT @dLWD = NULL
SELECT @dLWD = STUFF(STUFF(CAST(@LastWorkingDay AS varchar(8)), 7, 0, '-'), 5, 0, '-')
SELECT @dLWD

SET LANGUAGE German
/* YYYYMMDD */
SELECT @dLWD = NULL
SELECT @dLWD = CAST(@LastWorkingDay AS varchar(8))
SELECT 'YYYYMMDD(European)',@dLWD

/* YYYY-MM-DD */
SELECT @dLWD = NULL
SELECT @dLWD = STUFF(STUFF(CAST(@LastWorkingDay AS varchar(8)), 7, 0, '-'), 5, 0, '-')
SELECT 'YYYY-MM-DD(European)',@dLWD

SET LANGUAGE English
1
bthouinIT Analyst and developerAuthor Commented:
Thank you all for all your help, I'll be back soon with anther question...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Stored Proc

From novice to tech pro — start learning today.