writing function fordate

  • rosyshrestha7

    Old Hand

    Points: 342

    if check first as of date is current month or pior month

    if asofdate = prior month and TimClosestatus = 'C'

    then it should be month end date, if asofdate = current date and timclosestatus= null then asofdate should be current date

    set ANSI_NULLS ON

    go

    set QUOTED_IDENTIFIER ON

    go

    -- =============================================

    -- svf_GetPriorMonthEndDate_asofdate1

    -- =============================================

    create PROCEDURE [dbo].[svf_GetPriorMonthEndDate_asofdate1]

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

    SET NOCOUNT ON;

    declare @asofdate as datetime

    declare @currentdate as datetime

    declare @priormonthdate as datetime

    if @asofdate = @priormonthdate and TimClosestatus = 'C'

    then @asofdate = CONVERT(char(10), DATEADD(day, - (1 * DATEPART(day, @priormonthdate)), @priormonthdate), 101)

    else if @asofdate = @currentdate and TimClosestatus = 'null'

    then @asofdate = @currentdate

    end if

    GRANT EXECUTE ON svf_GetPriorMonthEndDate_asofdate1 TO PUBLIC

    end

    i am getting error : Msg 156, Level 15, State 1, Procedure svf_GetPriorMonthEndDate_asofdate1, Line 17

    Incorrect syntax near the keyword 'then'.

    Msg 156, Level 15, State 1, Procedure svf_GetPriorMonthEndDate_asofdate1, Line 20

    Incorrect syntax near the keyword 'then'.

    Msg 102, Level 15, State 1, Procedure svf_GetPriorMonthEndDate_asofdate1, Line 25

    Incorrect syntax near 'end'.

  • noeld

    SSC Guru

    Points: 96590

    there is no "THEN" in TSQL use BEGIN...END if you need demarcation.


    * Noel

  • rosyshrestha7

    Old Hand

    Points: 342

    can you please give example?

  • Dbs-887309

    Old Hand

    Points: 342

    1. There should not be any then , endif... etc

    2. You miss set

    IF @asofdate = @priormonthdate and TimClosestatus = 'C'

    BEGIN

    SET @asofdate = CONVERT(char(10), DATEADD(day, - (1 * DATEPART(day, @priormonthdate)), @priormonthdate), 101)

    END

    IF @asofdate = @currentdate and TimClosestatus = 'null'

    BEGIN

    SET @asofdate = @currentdate

    END

  • ChrisM@Work

    SSC Guru

    Points: 186120

    if @asofdate = @priormonthdate and TimClosestatus = 'C'

    What's 'TimClosestatus'? Where does it come from?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • SwePeso

    SSC-Dedicated

    Points: 39693

    See here http://www.sqlteam.com/article/datediff-function-demystified how DATEDIFF works.

    SELECT CASE DATEDIFF(MONTH, Col1, GETDATE())

    WHEN 0 THEN 'Same month'

    WHEN 1 THEN 'Prior month'

    ELSE 'Some other month'

    END AS MonthDescription

    FROM Table1


    N 56°04'39.16"
    E 12°55'05.25"

  • rosyshrestha7

    Old Hand

    Points: 342

    Timclosestatus is column name come from table

  • ChrisM@Work

    SSC Guru

    Points: 186120

    rosyshrestha7 (1/8/2009)


    Timclosestatus is column name come from table

    Come from table how? Please post the entire stored procedure.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • gary.proctor

    Mr or Mrs. 500

    Points: 580

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- svf_GetPriorMonthEndDate_asofdate1

    -- =============================================

    CREATE PROCEDURE [dbo].[svf_GetPriorMonthEndDate_asofdate1]

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

    SET NOCOUNT ON;

    DECLARE @asofdate AS DATETIME

    DECLARE @currentdate AS DATETIME

    DECLARE @priormonthdate AS DATETIME

    DECLARE @priormonthday AS INT

    SET @priormonthday = DATEPART(day, @priormonthdate)

    IF @asofdate = @priormonthdate and TimClosestatus = 'C'

    SET @asofdate = CAST(CONVERT(char(10), DATEADD(day, - @priormonthday, @priormonthdate), 101) AS DATETIME

    IF @asofdate = @currentdate and TimClosestatus = 'null'

    SET @asofdate = @currentdate

    GRANT EXECUTE ON svf_GetPriorMonthEndDate_asofdate1 TO PUBLIC

    END

    Try this on for size. The obivous things I noticed was that your syntax for an IF statemnt was using "THEN", and the BEGIN and ENDwere not required. Also, you need to reconvert the CONVERTed Char(10) back to a DATETIME using a CAST Statement... Hope This helps!

    -Gary

  • ChrisM@Work

    SSC Guru

    Points: 186120

    Good try Gary - but TimClosestatus comes from a table...

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • gary.proctor

    Mr or Mrs. 500

    Points: 580

    Chris - Good point - I missed that one. At least I hope the remainder of my syntax was correct (not that it will do a lick of good in this insatnce).

    🙂

Viewing 11 posts - 1 through 11 (of 11 total)

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