Function Determinism DATEADD Deterministic DATEDIFF Deterministic DATENAME Nondeterministic DATEPART Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week. DAY Deterministic GETDATE Nondeterministic GETUTCDATE Nondeterministic MONTH Deterministic YEAR Deterministic
select * from t where convert(varchar(10),t.d,120)='2008-07-29'
如果有限制年的就 比如2008年7月的就
FD > '2008-07-01' AND FD <'2008-08-01'
WHERE datepart(mm,FIELD)=7
declare @time datetime
set @time='2008-07-27 09:50:26.000'
select convert(varchar(10),@time,120)
联机丛书......
或baidu
看看convert时间函数的用法
有N种日期格式供你选择,总有一种适合你~
具体例子:
SELECT CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 1): 05/16/06
SELECT CONVERT(varchar(100), GETDATE(), 2): 06.05.16
SELECT CONVERT(varchar(100), GETDATE(), 3): 16/05/06
SELECT CONVERT(varchar(100), GETDATE(), 4): 16.05.06
SELECT CONVERT(varchar(100), GETDATE(), 5): 16-05-06
SELECT CONVERT(varchar(100), GETDATE(), 6): 16 05 06
SELECT CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
SELECT CONVERT(varchar(100), GETDATE(), 8): 10:57:46
SELECT CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
SELECT CONVERT(varchar(100), GETDATE(), 10): 05-16-06
SELECT CONVERT(varchar(100), GETDATE(), 11): 06/05/16
SELECT CONVERT(varchar(100), GETDATE(), 12): 060516
SELECT CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
SELECT CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
SELECT CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
SELECT CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
SELECT CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
SELECT CONVERT(varchar(100), GETDATE(), 24): 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
SELECT CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
SELECT CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
SELECT CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
SELECT CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
SELECT CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
SELECT CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
SELECT CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
SELECT CONVERT(varchar(100), GETDATE(), 108): 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
SELECT CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
SELECT CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
SELECT CONVERT(varchar(100), GETDATE(), 112): 20060516
SELECT CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
SELECT CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
SELECT CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
SELECT CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
SELECT CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
SELECT CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM
日期函授很多啊 year(),month(),dateadd()....
DATEADD Deterministic
DATEDIFF Deterministic
DATENAME Nondeterministic
DATEPART Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week.
DAY Deterministic
GETDATE Nondeterministic
GETUTCDATE Nondeterministic
MONTH Deterministic
YEAR Deterministic
/*
----------
2008-07-27(所影响的行数为 1 行)
*/2,[datetime]>='2008-7-1' and [datetime]<'2008-8-1'
[转]邹老大书上的
DECLARE @dt datetime
SET @dt=GETDATE()--1.短日期格式:yyyy-m-d
SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')--2.长日期格式:yyyy年mm月dd日
--A. 方法1
SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'
--B. 方法2
SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'--3.长日期格式:yyyy年m月d日
SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'--4.完整日期+时间格式:yyyy-mm-dd hh:mi:ss:mmm
SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)
--------------------------------
比如这样的日期类型2008-07-27 09:50:26.000怎样转化为这样的字符串类型2008-07-27?
比如我怎样查到7月的数据? DECLARE @dt datetime
SET @dt=GETDATE()select getdate() 当前日期--1.短日期格式:yyyy-m-d
SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-') 当前日期当前日期
------------------------
2008-07-29 11:23:45.200(所影响的行数为 1 行) 当前日期
---------------
2008-7-29(所影响的行数为 1 行)