[SQL] (Stored Procedure)获取当前日期(一)

设置服务器恒定时间,且获取当前服务器的时间。
[SQL Server]如果直接利用GETDATE()获取服务器当前时间,所获取的时间为server所在的本地的时间,这样如果数据中心在其他地域国家,或者不同server同步数据,直接利用GETDATE()便会出现问题。解决方案,在数据库创建一个存储当前business date的表:SysCurrentDate,之后在从该table中获取被设定好的当前Server date

(1): 存储business date的拟创表语句

CREATE TABLE [dbo].[SysCurrentDate](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CurrentDate] [datetime] NOT NULL,
	[CurrentTime] [datetime] NULL,
	[CreatedBy] [nvarchar](30) NOT NULL,
	[CreatedDate] [datetime] NOT NULL,
	[UpdatedBy] [nvarchar](30) NULL,
	[UpdatedDate] [datetime] NULL,
 CONSTRAINT [PK_SysCurrentDate] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

(2): 利用stored procedure获取该当前业务日期(注:如果该表中有数据则返回该当前date,如果没有数据则直接返回DB Server所在的当地date)
拟create sp语句:

-- =====================================================================================
-- Author: bluetata
-- ALTER date: 13-Oct-2017
-- Description:	
-- Get the current date from the table SysCurrentDate
-- If no item in the table, return GETDATE()
-- else
--		if CurrentTime is not null
--			return static date and time from current date
--		else
--			return static date from CurrentDate and running time from GETDATE()			
-- =====================================================================================
CREATE PROCEDURE [dbo].[sys_sp_GetCurrentDate] 

AS
BEGIN

	DECLARE @CurrentDate datetime = NULL
	DECLARE @CurrentTime datetime = NULL

	DECLARE @ReturnDate datetime = NULL

	SELECT TOP 1 @CurrentDate = CurrentDate, @CurrentTime = CurrentTime FROM SysCurrentDate

	IF(@CurrentDate IS NOT NULL)
	BEGIN

		IF(@CurrentTime IS NULL)
		BEGIN
			SET @ReturnDate = CONVERT(varchar(20),CONVERT(date, @CurrentDate)) + ' ' + convert(varchar(10), GETDATE(), 108)
		END
		ELSE
		BEGIN
			SET @ReturnDate = CONVERT(varchar(20),CONVERT(date, @CurrentDate)) + ' ' + convert(varchar(10), @CurrentTime, 108)
		END

	END
	ELSE
	BEGIN
			SET @ReturnDate = GETDATE()
	END


	SELECT @ReturnDate AS CurrentDate

END

本文原创由`bluetata`发布于blog.csdn.net、转载请务必注明出处。


Flag Counter

©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页