Post reply

writing function fordate

  • January 7, 2009 at 12:56 pm

    #99454

    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'.

  • January 7, 2009 at 1:39 pm

    #922884

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


    * Noel

  • January 7, 2009 at 2:42 pm

    #922936

    can you please give example?

  • January 8, 2009 at 2:41 am

    #923156

    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

  • January 8, 2009 at 2:50 am

    #923160

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

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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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

  • January 8, 2009 at 6:27 am

    #923259

    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"

  • January 8, 2009 at 12:04 pm

    #923613

    Timclosestatus is column name come from table

  • January 8, 2009 at 12:49 pm

    #923644

    rosyshrestha7 (1/8/2009)

    Timclosestatus is column name come from table

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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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

  • January 9, 2009 at 6:36 am

    #924108

    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

  • January 9, 2009 at 6:43 am

    #924120

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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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

  • January 9, 2009 at 6:49 am

    #924129

    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 10 (of 10 total)

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