[SQL](Stored Procedure)获取当前日期(一)
来源:互联网 发布:人参真的有用吗 知乎 编辑:程序博客网 时间:2024/06/06 02:38
设置服务器恒定时间,且获取当前服务器的时间。
[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] ASBEGINDECLARE @CurrentDate datetime = NULLDECLARE @CurrentTime datetime = NULLDECLARE @ReturnDate datetime = NULLSELECT TOP 1 @CurrentDate = CurrentDate, @CurrentTime = CurrentTime FROM SysCurrentDateIF(@CurrentDate IS NOT NULL)BEGINIF(@CurrentTime IS NULL)BEGINSET @ReturnDate = CONVERT(varchar(20),CONVERT(date, @CurrentDate)) + ' ' + convert(varchar(10), GETDATE(), 108)ENDELSEBEGINSET @ReturnDate = CONVERT(varchar(20),CONVERT(date, @CurrentDate)) + ' ' + convert(varchar(10), @CurrentTime, 108)ENDENDELSEBEGINSET @ReturnDate = GETDATE()ENDSELECT @ReturnDate AS CurrentDateEND
注:本文原创由`bluetata`发布于blog.csdn.net、转载请务必注明出处。
阅读全文
0 0
- [SQL](Stored Procedure)获取当前日期(一)
- SQL存储过程(Stored Procedure)
- Stored procedure & Sql Injection
- [SQL](Function)获取当前日期(二)
- 存储过程(Stored Procedure)使用(一)
- sql 获取当前日期
- 存储过程(Stored Procedure)
- 存储过程(Stored Procedure)
- 存储过程(Stored Procedure)
- 存储过程(Stored Procedure)
- 存储过程(Stored Procedure)
- Sql server stored procedure operating summary 01
- Sql server database stored procedure reference 01
- SQL Server 2000 Stored Procedure Programming
- Assignment 1: PL/SQL stored procedure P9
- Overview of SQL Server Stored Procedure
- Stored Procedure
- SQL当前日期获取技巧
- 第一天登录博客分享
- MySQL添加用户、删除用户与授权
- Linux I2C read eeprom 从应用层看系统
- Android应用APK签名
- Spring Boot JdbcTemplate ACE 模板
- [SQL](Stored Procedure)获取当前日期(一)
- Unity3d窗体透明
- 牛顿法与拟牛顿法学习笔记(二)拟牛顿条件
- 20171013memo
- leetcode 62 unique-paths
- JAVA设计模式之享元模式
- CodeForces 616E
- redis在spring和springboot中的使用方式以及遇到的坑
- 正则表达式