SQL Server Date

Subtract one day from datetime

DATEADD(day, -1, GETDATE())
DATEADD(hour, 1, GETDATE())

DATEDIFF

DATEDIFF ( [day|hour|minute|second|millisecond] , startdate , enddate )

SELECT DATEDIFF(DAY,  '2012-12-12 12:00:00', '2012-12-13 16:00:00') -- return 1

DECLARE @Start datetime
DECLARE @End datetime
SET @Start = GETDATE()
SET @End = GETDATE()
SELECT DATEDIFF(millisecond, @Start, @End)

Convert String to DateTime

CONVERT(datetime, '2012/12/12 08:30:00')

SELECT CONVERT(varchar, GETDATE(), 1) 

SELECT CONVERT(varchar, CONVERT(datetime, '2012-12-01'), 101) -- 12/01/2012

DATEPART

SELECT DATEPART(YEAR, GETDATE())
SELECT DATEPART(dd, GETDATE())
SELECT DATEPART(ms, GETDATE())

Query time zone

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 
'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone

Query time and time zone offset

SELECT SYSDATETIMEOFFSET() 

Convert DateTime to Date

SELECT CONVERT(date, getdate())

Query UTC time

SELECT GETUTCDATE()  -- 2019-04-04 05:42:45.170

Query more precision date

SELECT SYSDATETIME(), SYSUTCDATETIME() -- Returns a datetime2(7) value, e.g.: 2019-04-04 00:43:41.7743182

SQLServer

这篇文章: 有用 无用
创建 2018-09-29 05:35:37 / 更新 2019-04-07 03:55:33

Transact-SQL

DECLARE

DECLARE @gender varchar(30);   

DECLARE @find varchar(30) = 'Man%';

DECLARE @lastName varchar(30), @firstName varchar(30), @rows int;

DECLARE @age INT;

SET

SET @gender = 'Fema';

SET @gender += 'le';

SET @rows = (SELECT COUNT(*) FROM Customer);  

SET @age = 18;

SELECT

SELECT @gender = 'Man';

-- If CustomerID 1000 does not exists, @gender will retain its present value.
SELECT @gender = Gender FROM Customer WHERE CustomerID = 1000;

-- If CustomerID 1000 does not exists, @gender will be NULL
SELECT @gender = (SELECT Gender FROM Customer WHERE CustomerID = 1000);

GETDATE

SELECT GETDATE() as "Now"

IF

IF @gender IS NOT NULL
BEGIN
    { sql_statement | statement_block }
END

-- To define a statement block, use the control-of-flow keywords BEGIN and END.
IF Boolean_expression   
   { sql_statement | statement_block }   
[ ELSE   
   { sql_statement | statement_block } ]   

WHILE

DECLARE @cnt INT = 0;

WHILE @cnt < 3
BEGIN
    PRINT @cnt
    SET @cnt = @cnt + 1;
END;

SQLServer

这篇文章: 有用 无用
创建 2018-01-21 05:50:32 / 更新 2018-12-06 05:58:36

sql server Tips

Concept

DDL is Data Definition Language : it is used to define data structures.

For example, with SQL, it would be instructions such as create table, alter table, ...

DML is Data Manipulation Language : it is used to manipulate data itself.

For example, with SQL, it would be instructions such as insert, update, delete, ...

System

-- Find SQLServer Version
SELECT @@VERSION

-- Find names of the ServerName and ServiceName
SELECT @@SERVERNAME AS 'Server Name', @@SERVICENAME AS 'Service Name' 

-- Find names of the HostName and LoginUser
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser

-- Find all instance names (Command Prompt)
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL

-- Detect READ_COMMITTED_SNAPSHOT is enabled?
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'YourDatabase'

-- Detect current transaction level? 
DBCC useroptions

-- Find database object(table, procedures...)
select * from sys.objects where name like '%keyword%'

Tip

-- Default join behaviour test is inner join
DECLARE @A TABLE (x INT)
INSERT INTO @A
    SELECT 1 UNION ALL
    SELECT 2

DECLARE @B TABLE (x INT)
INSERT INTO @B
    SELECT 2 UNION ALL
    SELECT 3

SELECT 
    A.x AS 'A.x', 
    B.x AS 'B.x'
FROM @A A
JOIN @B B
    ON A.x = B.x

Recursion

WITH [Dates] as (
SELECT convert(datetime, '1/20/2017') as [Date]
UNION ALL
SELECT DATEADD(DAY, 1, [Date])
FROM [Dates]
WHERE [Date] < '12/21/2017'
) SELECT
    * from [Dates]
option (maxrecursion 0)

参考:


SQLServer

这篇文章: 有用 无用
创建 2018-01-17 06:04:08 / 更新 2019-04-11 02:36:03

SQLServer

当创建一个表的时候, 会对这个表的某些字段加一些约束或者默认值, 我们称为Constrainsts. 他们在创建过程中会产生一个名字, 如果没有特意给他们指定名字的话, 他们的名字将会是自动生成的.

于是, 当我们要删除或者修改constrainsts的时候, 会遇到一个问题, 我们不知道他们的名字

可用以下语句在数据库的系统表中查出constrainsts的名字

例如, 查询一个默认值的constrainsts的名字:

select [name] from sysobjects where id = (select cdefault from syscolumns where id = object_id(N'TableName') and name='ColumnName')

SQLServer

这篇文章: 有用 无用
创建 2012-01-12 21:42:54 / 更新 2018-04-20 08:49:17