Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    10

    Unanswered: Set vars in stored proc to first and last day of year

    Hello,

    Nice simple question from someone making there way with their first stored prcoedure. I am editing a procedure where I have hardcoded the two date variables to 01/01/06 and 31/12/06.

    How could I make this year sensitive so that next year the variables are set to 01/01/07 and 31/12/07 automatically.

    Many Thanks
    Neal

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    set @firstday = DATEADD(yyyy, DATEDIFF(yyyy, 0, GETDATE()), 0)
    set @lastday = DATEADD(yyyy, 1, @firstday) - 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    set variable =cast(cast(year(getdate()) as varchar)+ '-01-01' as datetime)
    set variable =cast(cast(year(getdate()) as varchar)+ '-12-31' as datetime)

    current year start and end

  4. #4
    Join Date
    Jul 2006
    Posts
    22
    with one cast less and maybe more "readable" for you?
    Code:
    declare @Fdoy datetime, @Ldoy datetime
    set @Fdoy = '01-jan-' + cast(year(getdate()) as varchar)
    set @Ldoy = '31-dec-' + cast(year(getdate()) as varchar)
    year(getdate()) returns the current year.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the versions which employ string handling are much slower than my method
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2004
    Posts
    10
    Many thanks for the feedback.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •