Date time time zone conversion built in function

Question

Date time time zone conversion built in function

N_Muller

SSCarpal Tunnel

Points: 4037
More actions
May 2, 2019 at 3:18 pm

#3629373
I have legacy databases that stores all data in U.S. Central Standard Time. Some servers are on CST and others are on UTC. Built-in function getdate() gets date in the server time, while getutcdate() gets date in UTC. I need a function that will always return date in CST irrespective of the server time.
I see two options:
1. replace every call to getdate() and getutcdate() to switchoffset(sysutcdatetime(),'-06:00') to make every datetime CST.
2. Create a UDF (i.e., fn_getcstdate) and have it return switchoffset(sysutcdatetime(),'-06:00')
The problem with the straight replacement with "switchoffset(sysutcdatetime(),'-06:00')" are that a) it's very verbose, and b) odds are that developers will never remember to use it.
The main problem with the UDF is performance. There is also the problem that the function must exist in every database. Calling the function dbo.fn_getcstdatetime() will never be the same as calling the straight replacement. A minor problem is having to prefix the calling to the UDF with "dbo.".
I also know that if I had SQL Server 2016 or later, I could create a native-compile scalar-valued UDF which would probably improve performance when compared with standard UDF. But that's not happening anytime soon.
Now my question: is there a way to create my own custom built-in function? I know I shouldn't add anything to the master database, but I'm willing to live with that if I can create my own custom built-in function. My custom built-in function will only use other built-in functions and have no input parameter.

Viewing 4 posts - 1 through 3 (of 3 total)

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

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

I'd use a function. You don't have to put in every db, you can call a function from another db. Let's say you created a "Shared_Functions" db to store the function. Then:

USE Shared_Functions;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.fn_getcstdate (
)
RETURNS TABLE
AS
RETURN ( SELECT SWITCHOFFSET(SYSUTCDATETIME(),'-06:00') AS cstdate )
/*end of function*/
GO
USE tempdb;
SELECT *
FROM Shared_Functions.dbo.fn_getcstdate()

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 2

Do you need to account for DST? Because the SWITCHOFFSET function wants your parameter to be DST adjusted when you pass it in.

N_Muller SSCarpal Tunnel Points: 4037 More actions · Answer 3

Thanks for the reply. No, I don't need DST. Dates in databases are always in CST, so I need to make sure to convert the computer time to CST and never use getdate().