
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetSystemDateTime](@ADateStr CHAR(8)) 
RETURNS DATETIME
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DATETIME

	DECLARE @TimeZone VARCHAR(100)
	DECLARE @CurrentDateStr CHAR(8)

	SELECT @TimeZone = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TimeZone'
	IF ISNULL(@TimeZone, '') = ''
	  SET @TimeZone = 'Romance Standard Time'

	IF ISNULL(@ADateStr, '') <> ''
	  SET @CurrentDateStr = @ADateStr
	ELSE
	  SELECT @CurrentDateStr = SystemDate FROM System WITH (NOLOCK) WHERE Number = 1

	SET @Result = CONVERT(DATETIME, @CurrentDateStr + ' ' + CONVERT(VARCHAR(8), CURRENT_TIMESTAMP AT TIME ZONE 'Romance Standard Time' AT TIME ZONE @TimeZone, 108))

	-- Return the result of the function
	RETURN(@Result)
END
GO

