{S0}这是一个展示我们的赞助商在CodeProject审查。这些评语是拟提供的产品和服务,我们认为有用和开发价值,的信息。简介
我不认为我在少数人的时候,当我承认,我学习编程技巧的最好的尝试的事情了!这quot; workbenchquot,使用在SQL Server中的日期和时间从quot出发位; usualquot;文章,你会发现简单的通话。它的设计,所以它可以粘贴在其全部进入查询分析器,SSMS中或其他GUI和执行个别的例子。
这quot; workbenchquot;使用SQL中的日期和时间上的结构,因此它可以在其entirity粘贴到查询分析器,SSMS的或其他的GUI和执行个别的例子。我不认为我在少数人的时候,当我承认,我学习编程技巧的最好的尝试的事情了!
它提供了工作的例子,以解决所有常见的日期相关的要求和问题,包括:输入和输出日期使用ISDATE检查有效日期提取部分日期寻找两个日期之间的的差异格式化日期计算日期(例如本月初)使用实际可行日期(例如,计算每天的总)
,也解决一些更棘手的问题,如UNIX时间戳转换为DateTime,并在本周初发现的DATETIME。
我想鼓励你去实验。一个从来没有失败来了惊喜,例如,我从来没有在写这之前,考虑使用"象",在搜索时DateTime字段,或使用{吨'2:40'}在存储过程中,作为一个文字日期。
同样,我总是希望看到尽可能多的例子,在SQL Server上的任何文章。有喜欢它没有得到思想准备。正式描述奇怪的肿块在他们的大脑的罚款,但我更愿意看到明确的解释与例子穿插!
如果我有任何一般性的建议,它是用datetime数据类型的优势和从未尝试绕过它的使用,存储日期或任何其他格式的时间,。我从来没有遇到一个这样的做法提供任何持久的利益的情况下。目录输入日期输入时报输出日期操作日期格式化日期计算日期日期转换使用日期输入日期
用户进入到形式的日期,你需要进入数据库中的datetime数据类型。日期可以被分配到DateTime变量或字符串列,但这些都是根据特定的语言,是当前存储的DATEFORMAT。书面orderin个月(M),天(D),一年(Y)是在其他国家是不同的。的us_english(MDY)不同的是来自英国(DMY)。通过显式设置的日期格式,你可以骑。
除其他事项外,您可以检查您当前的DateFormat,使用...DBCC USEROPTIONS
现在,证明这种错误可能会导致意想不到的错误... ...{C}
因此,任何基于字(如febbraio,fevereiro,2月)的日期表示将失败在任何其他的语言,使用的某一个月不同的单词。要看到当前的语言设置,使用sp_HelpLanguage
要导入foregn语言的日期,您必须更改连接的语言设置。 e.gSET language Italiano SELECT CAST('10 febbraio 2006' AS datetime)
--Changed language setting to Italiano.
--2006-02-10 00:00:00.000
--to see the current language settings, use
/* Otherwise SQL Server is fairly accomodating, and will do its best to make
sense of a date.*/
--all of the following return 2006-02-01 00:00:00.000
SET language british
SELECT CAST('1 feb 2006' AS datetime)--remember, this is language dependent
SELECT CAST('1 february 2006' AS datetime)--this too
SELECT CAST('01-02-06' AS datetime)
SELECT CAST('2006-02-01 00:00:00.000' AS datetime)
SELECT CAST('1/2/06' AS datetime)
SELECT CAST('1.2.06' AS datetime)
SELECT CAST('20060201' AS datetime)
--in SQL Server 2000 and 2005 you can specify dates in ISO 8601 format
SELECT CAST('2006-02-01T00:00:00' AS datetime)
SELECT CAST('2006-02-01T00:00:00.000' AS datetime)
--and you'll be able to enter in this format whatever the settings!
ANSI标准日期使用括号,'D'标志指定的日期,日期字符串
ANSI标准DateTime使用"TS",而不是"D",并增加了小时,分钟和秒的日期(使用24小时制)
SELECT { ts '2006-02-01 00:00:00' }
如果使用CONVERT函数,你可以通过选择正确的CONVERT样式(103是英国/法国的DD / MM / YYYY的格式(见所有样式的列表后覆盖DATEFORMAT)
IsDate函数SET language us_english
SELECT CONVERT(DateTime,'25/2/2006',103) --works fine
--whereas the 100 style uses the default supplied by the dateformat.
SELECT CONVERT(DateTime,'25/2/2006',100) --error!
ISDATE(表达)的功能是用于检查字符串,看看他们是有效的日期。它是依赖于语言的
ISDATE(expression)返回1,如果表达的是一个有效的日期(根据LANGUAGE和DATEFORMAT面具)和0,如果它不
下面的演示使用ISDATE测试输入字符串为日期。
输入时报SET LANGUAGE british SET nocount ON
DECLARE @DateAsString VARCHAR(20),
@DateAsDateTime DateTime
SELECT @DateAsString='2 february 2002'
SELECT [input]=@DateAsString
IF (ISDATE(@DateAsString)=1)
BEGIN
SELECT @DateAsDateTime=@DateAsString
SELECT [the Date]=COALESCE(CONVERT(CHAR(17),@DateAsDateTime,113),
'unrecognised')
END
ELSE
SELECT [the Date] ='That was not a date'
次可以很容易地输入到SQL Server。有没有单独的时间和日期数据类型只存储时间或只有日期。这是没有必要的。设置一个datetime时,如果只指定时间,日期是假设1900年1月,在新的千年。
启动一年,如果只有一个指定日期,时间默认
如午夜。SELECT CAST ('17:45' AS datetime) -- 1900-01-01 17:45:00.000
SELECT CAST ('13:20:25:850' AS datetime) -- 1900-01-01 13:20:25.850
SELECT CAST ('14:30:20.9' AS datetime) -- 1900-01-01 14:30:20.900
SELECT CAST ('3am' AS datetime) -- 1900-01-01 03:00:00.000
SELECT CAST ('10 PM' AS datetime) -- 1900-01-01 22:00:00.000
SELECT CAST ('02:50:20:500AM' AS datetime) -- 1900-01-01 02:50:20.500
SELECT CONVERT (DateTime,'02:50:20',108) -- 1900-01-01 02:50:20.000
--And times can be converted back from the DATETIME into the ascii VARCHAR
--version as follows...
SELECT CONVERT(VARCHAR(20),GETDATE(),108) -- 15:08:52
--108 is the hh:mm:ss CONVERT style (See next section for the complete list)
SELECT LTRIM(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7))-- 3:10PM
SELECT LTRIM(RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)) -- 3:19:18:810PM
等。
,您可以输入时间以不同的方式(注括号,大括号SELECT { t '09:40:00' }
出人意料地给出了09.40的今天,而不是9:40,1900年1月1日! (如人们所预料的其他时间输入例子)
输出日期--this is valid in a stored procedure too
CREATE PROCEDURE #spExperiment AS
SELECT { t '09:40:00' }
GO
EXEC #spExperiment
日期可以作为字符串输出方式使用CONVERT函数和CONVERT样式,这些样式与最流行的日期格式对应的数字代码。你得到更多的功能比CAST函数CONVERT函数。
"转换样式覆盖DATEFORMAT的设置,但使用当前语言设置日期格式使用月份的名称。
{ BR}如果你运行下面的代码,你会得到一个结果,说明了所有内置的格式,使用当前的日期和时间
操作日期DECLARE @types TABLE(
[2 digit year] INT NULL,
[4 digit year] INT NOT NULL,
name VARCHAR(40))
SET LANGUAGE british SET nocount ON
--Each select statement is followed by an example of a string that uses the
--style
INSERT INTO @types
SELECT NULL,100,'Default'--Oct 17 2006 9:29PM
INSERT INTO @types
SELECT 1,101, 'USA'--10/17/06 or 10/17/2006
INSERT INTO @types
SELECT 2,102, 'ANSI'--06.10.17 or 2006.10.17
INSERT INTO @types
SELECT 3,103, 'British/French'--17/10/06 or 17/10/2006
INSERT INTO @types
SELECT 4,104, 'German'--17.10.06 or 17.10.2006
INSERT INTO @types
SELECT 5,105, 'Italian'--17-10-06 or 17-10-2006
INSERT INTO @types
SELECT 6,106, 'dd mon yy'--17 Oct 06 or 17 Oct 2006
INSERT INTO @types
SELECT 7,107, 'Mon dd, yy'--Oct 17, 06 or Oct 17, 2006
INSERT INTO @types
SELECT 8,108, 'hh:mm:ss' --21:29:45 or 21:29:45
INSERT INTO @types
SELECT NULL,109, 'Default + milliseconds'--Oct 17 2006 9:29:45:500PM
INSERT INTO @types
SELECT 10,110,'USA' --10-17-06 or 10-17-2006
INSERT INTO @types
SELECT 11,111,'JAPAN'--06/10/17 or 2006/10/17
INSERT INTO @types
SELECT 12,112,'ISO'--061017 or 20061017
INSERT INTO @types
SELECT NULL,113,'Europe default(24h) + milliseconds'--17 Oct 2006 21:29:45:500
INSERT INTO @types
SELECT 14,114,'hh:mi:ss:mmm (24h)' --21:29:45:500 or 21:29:45:500
INSERT INTO @types
SELECT NULL,120,'ODBC canonical (24h)'--2006-10-17 21:29:45
INSERT INTO @types
SELECT NULL,121, 'ODBC canonical (24h)+ milliseconds'
--2006-10-17 21:29:45.500
INSERT INTO @types
SELECT NULL,126, 'ISO8601'--2006-10-17T21:29:45.500
--insert into @types Select null,127, 'ISO8601 with time zone'
span>
--SQL Server 2005 only!
INSERT INTO @types
SELECT NULL,130, 'Hijri'--25 ????? 1427 9:33:21:340PM
INSERT INTO @types
SELECT NULL,131, 'Hijri'--25/09/1427 9:29:45:500PM
SELECT [name], [2 digit year]=COALESCE(CONVERT(VARCHAR(3),[2 digit year]),'-'),
[example]=CASE WHEN [2 digit year] IS NOT NULL
THEN CONVERT(VARCHAR(30),GETDATE(),[2 digit year])
ELSE '-' END,
[4 digit year]=COALESCE(CONVERT(VARCHAR(3),[4 digit year]),'-'),
[example]=CASE WHEN [4 digit year] IS NOT NULL
THEN CONVERT(VARCHAR(30),GETDATE(),[4 digit year])
ELSE '-' END
FROM @types
获取当前日期,可以通过三种功能:SELECT GETDATE() --the local date and time
SELECT GETUTCDATE() --the UTC or GMT date and time
ELECT CURRENT_TIMESTAMP--synonymous with GetDate()
当一个DateTime提取部分,你有一些方便的功能,返回整数的一天,一个月,一年...在这里,我们每天,每月为整数年份SELECT DAY(GETDATE()),MONTH(GETDATE()),YEAR(GETDATE())
天的月份和年份简写为等效的datepart命令,但更普遍的使用DatePart函数更为灵活DATEADD
DATEADD将实际增加了数年,季,月,周,天,小时,分钟,秒或毫秒您specifced日期今年(YY或YYYY)季(QQ或q)月(毫米或M)本周(周或WW)日(DAYOFYEAR,DY,Y,天,DD,D,平日或DW)小时(hh)
分钟(MI或N),第二个(SS或S)毫秒(ms)
,在这些例子中,我们比较了DateAdded日期的日期,所以你可以看到DATEADD是有它的效果SELECT '2007-01-01 00:00:00.000', DATEADD(YEAR,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(quarteer,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(MONTH,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(dayofyear,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(DAY,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(week,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(weekday,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(hour,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000', DATEADD(minute,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(second ,100,'2007-01-01 00:00:00.000')
SELECT '2007-01-01 00:00:00.000',
DATEADD(millisecond,100,'2007-01-01 00:00:00.000')
获取当前日期,可以通过三种功能
DATEDIFFSELECT GETDATE() --the loval date and time
SELECT GETUTCDATE() --the UTC or GMT date and time
SELECT CURRENT_TIMESTAMP--synonymous with GetDate()
DATEDIFF返回一个整数,两个日期以年表示,季度,月,周,日,小时,分钟,秒或毫秒(计数的界限)之间的差异。SELECT DATEDIFF(DAY,'1 feb 2006','1 mar 2006')--28
SELECT DATEDIFF(DAY,'1 feb 2008','1 mar 2008')--29. Hmm must be a leap year!
我们将给予其使用的一些实际的例子,后来在车间DATENAME
与DATEPART,它返回一个整数,DATENAME返回一个nvarchar代表年份,季度,月,周,每周一天,一天的一年,小时,分钟,第二次或毫秒内的日期。在syslanguages表中的值的月份和星期几。
DATEPARTSELECT DATENAME (YEAR,GETDATE()) --2006
SELECT DATENAME (quarter,GETDATE()) --4
SELECT DATENAME (MONTH,GETDATE()) --October
SELECT DATENAME (dayofyear,GETDATE()) --285
SELECT DATENAME (DAY,GETDATE()) --12
SELECT DATENAME (week,GETDATE()) --42
SELECT DATENAME (weekday,GETDATE()) --Thursday
SELECT DATENAME (hour,GETDATE()) --9
SELECT DATENAME (minute,GETDATE()) --32
SELECT DATENAME (second ,GETDATE()) --8
SELECT DATENAME (millisecond,GETDATE()) --875
DATEPART返回一个整数,表示在第一个参数要求的日期部分。您可以使用一年(YY或YYYY),季度(QQ或Q),月(毫米或M),DAYOFYEAR(DY或Y)天(DD或D),周(周或WW),周日(DW),小时(HH),分钟(MI或n),第二(SS或S),或毫秒(ms)
格式化日期SELECT DATEPART(YEAR,GETDATE()) --2006
SELECT DATEPART(quarter,GETDATE()) --4
SELECT DATEPART(MONTH,GETDATE()) --10
SELECT DATEPART(dayofyear,GETDATE()) --285
SELECT DATEPART(DAY,GETDATE()) --12
SELECT DATEPART(week,GETDATE()) --42
SELECT DATEPART(weekday,GETDATE()) --4
SELECT DATEPART(hour,GETDATE()) --9
SELECT DATEPART(minute,GETDATE()) --32
SELECT DATEPART(second ,GETDATE()) --8
SELECT DATEPART(millisecond,GETDATE()) --875
计算和格式化日期的例子例如计算日期
日期转换-- now
SELECT GETDATE()
-- Start of today (first thing)
SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
-- Start of tomorrow (first thing)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)
-- Start of yesterday (first thing)
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,-1,GETDATE()),113) AS datetime)
-- This time Next thursday (today if it is thursday)
SELECT DATEADD(DAY,((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+2)) % 7),
GETDATE())
-- This time Last friday (today if it is friday)
SELECT DATEADD(DAY,-((7-DATEPART(dw,GETDATE())+(((@@Datefirst+3)%7)+3)) % 7),
GETDATE())
-- Two hours time
SELECT DATEADD(hour,2,GETDATE())
-- Two hours ago
SELECT DATEADD(hour,-2,GETDATE())
-- Same date and time last month
SELECT DATEADD(MONTH,-1,GETDATE())
-- Start of the month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),GETDATE(),113),8) AS datetime)
-- Start of last month
SELECT CAST('01 ' + RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,-1,GETDATE()),113),8)
AS datetime)
-- Start of next month
SELECT CAST('01 '+ RIGHT(CONVERT(CHAR(11),DATEADD(MONTH,1,GETDATE()),113),8)
AS datetime)
-- Ten minutes ago
SELECT DATEADD(minute,-10,GETDATE())
-- Midnight last night
SELECT CAST(CONVERT(CHAR(11),GETDATE(),113) AS datetime)
-- Midnight tonight
SELECT CAST(CONVERT(CHAR(11),DATEADD(DAY,1,GETDATE()),113) AS datetime)
-- Three weeks ago
SELECT DATEADD(week,-3,GETDATE())
-- Start of the week (this depends on your @@DateFirst setting)
span>
SELECT DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE())
-- last year
SELECT DATEADD(YEAR,-1,GETDATE())
-- new year, this year
SELECT CAST('01 Jan'+ DATENAME(YEAR,GETDATE()) AS datetime)
-- new year, last year
SELECT CAST('01 Jan'+ DATENAME(YEAR,DATEADD(YEAR,-1,GETDATE())) AS datetime)
-- next christmas
SELECT CASE WHEN DATEPART(dy,GETDATE()) <
DATEPART(dy,'25 Dec' + DATENAME(YEAR,GETDATE()))
THEN CAST('25 Dec'+ + DATENAME(YEAR,GETDATE()) AS datetime)
ELSE CAST('25 Dec'+ CAST(DATEPART(YEAR,GETDATE())+1 AS VARCHAR) AS datetime)
END
datetime数据类型存储日期和时间数据,从1753年1月1,到9999年12月31日,一个3.33毫秒的精度。价值均四舍五入。
值存储为两个4字节的整数:。前4个字节存储的天数 - 从基准日,1900年1月1日起,。基准日期是系统参考日期。第二个4字节存储午夜后的毫秒数表示一天的时间。
早于1753年1月1日起,为datetime的值是不允许的。
当从SQL Server日期转换为Unix时间戳,日期是四舍五入到最接近的第二(UNIX时间戳只精确到最接近的第二个)SQL Server日期UNIX时间戳(自1 / 1 /秒基于标准划时代1970年)
使用日期SELECT DATEDIFF(second,'1/1/1970',GETDATE())
-- UNIX timestamp to SQL Server
SELECT DATEADD(second, 1160986544, '1/1/1970')
存储日期时,我们总是datetime数据类型。不要感到动心使用技巧,如存储为整数的一年,一个月或一天的想法,这将有助于检索和报告的聚集,。它从来不会。操纵的日期时间是如此重要,它是高度优化的SQL Server的性能。很好的工作基础上的日期时间索引,排序正确,并允许快速分区上的各种标准,如周,月,年,日等。例如,如果你存储列表按日期购买一个表,如购买,你可以找到前一周的总和... ...SELECT HERE purchaseDate LIKE '%9:40%'
--or all purchases in the month of february
SELECT COUNT(*) FROM [purchases]
WHERE purchaseDate LIKE '%feb%'
--all purchases where there is a 'Y' in the month (matches only May!)
SELECT DATENAME(MONTH, insertionDate), COUNT(*) FROM [purchases]
WHERE purchaseDate LIKE '%y%'
GROUP BY DATENAME(MONTH, purchaseDate)
这就好比伎俩是有限的使用,并应具有相当的谨慎使用,因为它使用的手腕得到其结果{S1}