• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

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
0
bthouin
Asked:
bthouin
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @sLWD char(8)
DECLARE    ------------------------------------------ remove this line
0
 
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
 
bthouinAuthor Commented:
oh sh...

Yes, at least it runs... thanks, I'm an idiot ...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bthouinAuthor 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
 
bthouinAuthor Commented:
Thank you all for all your help, I'll be back soon with anther question...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now