Post reply

Must declare the scalar variable

  • May 16, 2008 at 12:03 am

    #122004

    I am relatively new to sql and may be "biting off more than I can chew"

    I would like to give the variable @sunday - last sundays date relative to todays date. But it keeps reporting an error that I must declare the scalar variable @sunday. I have checked some previous posts and found that collation played a part in other errors. My collation is

    SQL_Latin1_General_CP1_CI_AS.

    DECLARE @sunday datetime;

    SELECT @sunday = (Select DATEADD(week, DATEDIFF(week, 6, getdate()) -1, 6));

    Even if I apply a date directly to @sunday I get the error.

    Any suggestions would be appreciated.

    Thanking you in advance

  • May 16, 2008 at 1:08 am

    #816282

    I didnt fiind any problem in executing the above statements.

    Must declare scalar variable - this error appears only when u execute the following statement

    SELECT @sunday = (Select DATEADD(week, DATEDIFF(week, 6, getdate()) -1, 6));

    without declaring the variable @sunday, so if u put the declare statement before the select statement the query should execute

  • May 16, 2008 at 1:16 am

    #816287

    But your formula returns the Sunday prior to the previous Sunday. And be careful when using DateDiff(week... - it's a 'wicked' creature.

    Try this formula:

    select dateadd(d,-1*datediff(d,'20080106',getdate())%7,getdate())

    What it does is

    - take difference between todays date and a known Sunday date MOD 7, i.e. it essentially tells how many days I'm past the last Sunday

    - you then subtract this from today's date (really adding it multiplied by -1)

  • May 16, 2008 at 4:46 am

    #816345

    Because you are not selecting the whole script and also the correct way of writing that is as follow:

    DECLARE @sunday datetime;

    SET @sunday = (Select DATEADD(week, DATEDIFF(week, 6, getdate()) -1, 6));

    SELECT @sunday

    Basit Ali Farooq
    MCITP Database Administrator
    Microsoft Certified Professional Developer (Web Applications)
    Microsoft Certified Database Administrator
    Microsoft Certified Systems Engineer
    Microsoft Certified Systems Administrator
    CIW Security Analyst
    Cisco Certified Network Associate

  • May 16, 2008 at 4:51 am

    #816347

    When I run this, it returns 2008-05-04 which isn't even a Sunday and which with respect to today 's date(2008-05-16) should return 2008-05-11.

  • May 16, 2008 at 4:59 am

    #816352

    Hi Micheal,

    Try this..

    DECLARE @sunday datetime;

    SET @sunday = (select dateadd(d,-1*datediff(d,'20080106',getdate())%7,getdate()));

    SELECT @sunday

    Basit Ali Farooq
    MCITP Database Administrator
    Microsoft Certified Professional Developer (Web Applications)
    Microsoft Certified Database Administrator
    Microsoft Certified Systems Engineer
    Microsoft Certified Systems Administrator
    CIW Security Analyst
    Cisco Certified Network Associate

  • May 18, 2008 at 4:19 pm

    #817051

    I would like to thank everyone who replied. Over the weekend I did a new install and everything is now doing as it is expected. It was also comforting that your help was at the other end of my internet connection. I really appreciated the confirmation that the code I was using (or trying to use) actually should work.

    Thanking you all again

  • March 19, 2011 at 11:38 am

    #1300938

    You're a genuis, just in case you didn't already know that... I was stuck on this same problem and your post saved my day... THANK YOU... 😀

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply