DATEDIFF ON MAX DATE

  • Hi there getting the following error when I run the SELECT STATEMENT.

    Server: Msg 156, Level 15, State 1, Line 11

    Incorrect syntax near the keyword 'as'.

    SELECT dbo.[CORE_CO-PLACE].[place-ref],

    dbo.[CORE_CO-PLACE].[address1],

    dbo.[CORE_CO-PLACE].[address2],

    dbo.[CORE_CO-PLACE].[address3],

    dbo.[IH_IH-LOCATION].[location-type]

    ,dbo.[IH_RE-TENANCY].[tncy-end]AS 'Tenancy End'

    ,MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS]) AS 'RecievedKeysDate'

    ,MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) AS 'ReadyToLetDate'

    ,dbo.[IH_IH-LOCATION-HIST].[START-DATE] AS 'VoidFromDate'

    ,DATEDIFF(day, MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS]),

    MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Days_with_Voids

    FROMdbo.[CORE_CO-PLACE]

    LEFT OUTER JOIN dbo.[IH_IH-LOCATION]

    ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]

    LEFT OUTER JOIN dbo.[IH_RE-TNCY-PLACE]

    ON dbo.[IH_IH-LOCATION].[PLACE-REF] = dbo.[IH_RE-TNCY-PLACE].[PLACE-REF]

    LEFT OUTER JOIN dbo.[IH_RE-TENANCY]

    ON dbo.[IH_RE-TNCY-PLACE].[TNCY-SYS-REF] = dbo.[IH_RE-TENANCY].[TNCY-SYS-REF]

    LEFT OUTER JOIN dbo.[IH_IH-LOCATION-STAT-HIST]

    ON dbo.[IH_IH-LOCATION-STAT-HIST].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]

    LEFT OUTER JOIN dbo.[IH_IH-PLACE-CHG]

    ON dbo.[CORE_CO-PLACE].[PLACE-REF] = dbo.[IH_IH-PLACE-CHG].[PLACE-REF]

    LEFT OUTER JOIN dbo.[IH_IH-LOCATION-HIST]

    ON dbo.[IH_IH-LOCATION-HIST].[PLACE-REF] = dbo.[IH_IH-LOCATION].[PLACE-REF]

    WHEREdbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS] IS NOT NULL

    AND CONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) between '30/11/2010' and '01/01/2011'

    ANDCONVERT(DATETIME, dbo.[IH_RE-TENANCY].[tncy-end]) < dbo.[IH_IH-LOCATION-HIST].[START-DATE]

    ANDdbo.[IH_IH-LOCATION-HIST].[LOCATION-STS] = 'V'

    ANDdbo.[IH_IH-LOCATION].[location-type] <> 'GARAGE'

    GROUP BY dbo.[CORE_CO-PLACE].[place-ref],

    dbo.[CORE_CO-PLACE].[address1],

    dbo.[CORE_CO-PLACE].[address2],

    dbo.[CORE_CO-PLACE].[address3],

    dbo.[IH_IH-LOCATION].[location-type]

    ,dbo.[IH_RE-TENANCY].[tncy-end]

    ,dbo.[IH_IH-LOCATION-HIST].[START-DATE]

    It's definatley linked to the ,DATEDIFF(day, MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS]), MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET]) as Days_with_Voids

    Any Ideas?

    Thanks

  • think you need an extra closing bracket before Days_with_Voids. currently you just have one.

    )) as Days_with_Voids

  • Yes, As davidandrews13 mentioned closing parenthesis was missing for datediff function

  • Thanks Guys.

    Done the trick

  • Messed up this post - let me explain it better what I am trying to achieve

  • Morning Guys,

    I'm trying to do the following.

    In my SELECT Statement I am using DATEDIFF to calculate some difference in dates. As below -

    SELECT DATEDIFF(day, MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[RECEIVED-KEYS])

    ,MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) AS 'Days_with_Voids'

    ,DATEDIFF(day, dbo.[IH_RE-TENANCY].[tncy-end]

    ,MAX(dbo.[IH_IH-LOCATION-STAT-HIST].[READY-TO-LET])) AS 'Total_Days_to_RTL'

    I now want to get the difference between these two fields.

    What I have done is place ,'Days_with_Voids' - 'Total_Days_to_RTL' AS 'Difference'

    However I get the following error -

    Server: Msg 403, Level 16, State 1, Line 1

    Invalid operator for data type. Operator equals subtract, type equals varchar.

    Can you please advise how I go about correcting this?

Viewing 6 posts - 1 through 5 (of 5 total)

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