check stored procedure: Convert a datetime of specific city of timezone to another

Question

I'm trying to create Stored Proc who converts a datetime of specific city of timezone to another using this article: Effectively Converting dates between UTC and Local (ie. PST) time in SQL 2005

    ALTER PROCEDURE [dbo].[Get_TimeOfTimeZone]
    (
        @DateTimeFrom   datetime,
        @CityNameFrom   nvarchar(MAX),
        @CityNameTo     nvarchar(MAX),
        @ResultDate     datetime OUTPUT
    )

AS
    SET NOCOUNT ON

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    Declare @Query nvarchar(1000)
    Declare @ParmDefinition nvarchar (500)

    SET @Query = N''
    SET @Query = @Query + N'SELECT tz.Offset, tz.TimeZoneID '
    SET @Query = @Query + N'FROM TimeZoneCity, TimeZones tz WITH (NOLOCK) '
    --SET @Query = @Query + N'INNER JOIN    TimeZones WITH (NOLOCK) ON TimeZoneCity.TimeZoneID = tz.TimeZoneID '
    SET @Query = @Query + N'LEFT JOIN   TimeZoneDaylightSavings WITH (NOLOCK) ON TimeZoneDaylightSavings.TimeZoneID = tz.TimeZoneID '
    SET @Query = @Query + N'    WHERE TimeZoneCity.CityName = @CityNameTo '
    SET @Query = @Query + N'    AND TimeZoneCity.TimeZoneID = tz.TimeZoneID '
    SET @Query = @Query + N'        AND @DateTimeFrom >= TimeZoneDaylightSavings.BeginDst AND @DateTimeFrom < TimeZoneDaylightSavings.EndDst '

    SET @Query = @Query + N'SET @ResultDate =   DATEADD(HH, tz.Offset + CASE WHEN tz.TimeZoneID IS NOT NULL THEN 1 ELSE 0 END, '
    SET @Query = @Query + N'                            DATEADD(HH, (SELECT TimeZones.Offset '
    SET @Query = @Query + N'                                            FROM TimeZones, TimeZoneCity WITH (NOLOCK) ' 
    SET @Query = @Query + N'                                            WHERE TimeZoneCity.CityName = @CityNameFrom '
    SET @Query = @Query + N'                                            AND TimeZoneCity.TimeZoneID = TimeZones.TimeZoneID), '
    SET @Query = @Query + N'                                    @DateTimeFrom)) '

    SET @ParmDefinition = N'@ResultDate datetime, @DateTimeFrom datetime, @CityNameFrom nvarchar(MAX), @CityNameTo nvarchar(MAX) '

    EXECUTE sp_executesql @Query, @ParmDefinition, @ResultDate = @ResultDate, @DateTimeFrom = @DateTimeFrom, @CityNameFrom = @CityNameFrom, @CityNameTo = @CityNameTo

    SET NOCOUNT OFF 
RETURN

When i execute it, i have these errors:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tz.Offset" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tz.TimeZoneID" could not be bound.

Can anybody check my stored procedure and help me? Thanks a lot

score 0 · Accepted Answer · answered Mar 19 '13 at 16:32

0

You are mixing JOIN types in your query. The following line is joined using commas and a WHERE clause

FROM TimeZoneCity, TimeZones tz WITH (NOLOCK)

Since you are mixing the join syntax, the alias tz is not available. You should the same syntax throughout. This line should be:

SET @Query = @Query + N'FROM TimeZoneCity 
                        INNER JOIN TimeZones tz WITH (NOLOCK) 
                              ON TimeZoneCity.TimeZoneID = tz.TimeZoneID'

answered Mar 19 '13 at 16:32

Taryn

242,637
56
362
405

Thanks bluefeet, this is a good point, but it did not solve my problem, I have the same error. Maybe second part of query is not correct?? – Vinipuh Mar 19 '13 at 17:37
If you remove the `SET @ResultDate` portion of your query does it work? – Taryn Mar 19 '13 at 17:41
@Vinipuh It is not clear what you are trying to do with that second part of your query. – Taryn Mar 19 '13 at 18:00

check stored procedure: Convert a datetime of specific city of timezone to another

1 Answers1