SQL Server 日期时间类型

示例1

 1 DECLARE @date1 DATE
 2 DECLARE @date2 DATETIME
 3 DECLARE @date3 DATETIME2
 4 DECLARE @date4 DATETIMEOFFSET  --sql server 2008 新增
 5 
 6 SET @date1=GETDATE()
 7 SET @date2=GETDATE()
 8 SET @date3=GETDATE()
 9 SET @date4=GETDATE()
10 
11 SELECT @date1,@date2,@date3,@date4
12 
13 -->2014-05-03    
14 -->2014-05-03 02:04:43.003    
15 -->2014-05-03 02:04:43.0030000    
16 -->2014-05-03 02:04:43.0030000 +00:00
View Code
 1 SELECT CONVERT(NVARCHAR(255),GETDATE(),112)
 2 -->20140503
 3 
 4 SELECT CONVERT(NVARCHAR(255),GETDATE(),120)
 5 -->2014-05-03 02:20:41
 6 
 7 SELECT CONVERT(NVARCHAR(255),GETDATE(),121)
 8 -->2014-05-03 02:24:47.580
 9 
10 SELECT CONVERT(DATETIME,'2051-1-1',120)
11 -->2051-01-01 00:00:00.000
12 
13 SELECT CONVERT(DATETIME,'20510101',121)
14 -->2051-01-01 00:00:00.000
View Code

 

 1 CREATE TABLE test1 ( Name NVARCHAR(20) );
 2 GO
 3 INSERT  INTO test1
 4         SELECT  'a'
 5         UNION ALL
 6         SELECT  'b'
 7         UNION ALL
 8         SELECT  'c'
 9         UNION ALL
10         SELECT  'd'
11         UNION ALL
12         SELECT  'e'
13         UNION ALL
14         SELECT  'f' 
15 ---------------------------------
View Code

 

 

SELECT  CURRENT_TIMESTAMP 'standard datetime',
        GETDATE() 'Transact-SQL datetime',
        DATEPART(YEAR, GETDATE()) 'datepart year',
        YEAR(GETDATE()) 'year function', DATEPART(HOUR, GETDATE()) 'hour'
--=========================================

posted @ 2014-05-03 02:09  海阔天空XM  阅读(255)  评论(0)    收藏  举报