declare @dt datetime set @dt='2008-09-27 09:22:21.280' select convert(varchar(10),@dt,120)
-- 日期函数 select getdate() -- 返回当前系统日期 --dateadd -- 返回日相加后的日期 day select dateadd (dd,5,'1999-9-9') -- 返回月相加后的日期 month select dateadd (month,5,'1999-9-9') -- 返回年相加后的日期 year select dateadd (yy,5,'1999-9-9')--DateDiff select datediff(mm,'2003-4-5','2007-6-7')--dateName --weekday (dw) 日期部分返回星期几(星期天、星期一等)。--DATENAME --返回代表指定日期的指定日期部分的字符串。--语法 --DATENAME ( datepart , date )--参数 --datepart--是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。--日期部分 缩写 --year yy, yyyy --quarter qq, q --month mm, m --dayofyear dy, y --day dd, d --week wk, ww --weekday dw --Hour hh --minute mi, n --second ss, s --millisecond ms select datename(dw,'1999-9-9')--datepart select datepart(day,'1999-9-11')
SQL 日期处理函数.我自己写的. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetMonthDays]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[fGetMonthDays] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetMonthFirstDay]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[fGetMonthFirstDay] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetMonthLastDay]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[fGetMonthLastDay] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetYearFirstDay]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[fGetYearFirstDay] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetYearLastDay]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[fGetYearLastDay] GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--取某日期的月天数 CREATE FUNCTION dbo.fGetMonthDays ( @dDateNow SMALLDATETIME ) RETURNS INT AS BEGIN DECLARE @iMonthDays INT DECLARE @dMonthFirstDay SMALLDATETIME DECLARE @dMonthLastDay SMALLDATETIME
SELECT @dMonthFirstDay = dbo.fGetMonthFirstDay(@dDateNow), @dMonthLastDay = dbo.fGetMonthLastDay(@dDateNow) SELECT @iMonthDays = DATEDIFF (d,@dMonthFirstDay,@dMonthLastDay)+1 RETURN @iMonthDays END --版权所有:lyx GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--取某日期的月第一天 CREATE FUNCTION dbo.fGetMonthFirstDay ( @dDateNow SMALLDATETIME ) RETURNS VARCHAR(12) AS BEGIN DECLARE @dMonthFirstDay VARCHAR(12) SELECT @dMonthFirstDay = CONVERT(VARCHAR(10),CAST(CAST(YEAR(@dDateNow)AS CHAR(4))+'/'+CAST(MONTH(@dDateNow)AS CHAR(2))+'/01' AS DATETIME),120)
RETURN @dMonthFirstDay END --版权所有:lyxGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO--取某日期的月最后一天 CREATE FUNCTION dbo.fGetMonthLastDay ( @dDateNow SMALLDATETIME ) RETURNS VARCHAR(12) AS BEGIN DECLARE @dMonthLastDay VARCHAR(12)
SELECT @dMonthLastDay = CONVERT(VARCHAR(10),DATEADD(d,-1,CAST(CAST(YEAR(@dDateNow)AS CHAR(4))+'/'+CAST(MONTH(DATEADD(m,1, @dDateNow)) AS CHAR(2))+'/01' AS DATETIME)),120)
RETURN @dMonthLastDay END --版权所有:lyx GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--取某日期的年第一天 CREATE FUNCTION dbo.fGetYearFirstDay ( @dDateNow SMALLDATETIME ) RETURNS VARCHAR(12) AS BEGIN DECLARE @dYearFirstDay VARCHAR(12) SELECT @dYearFirstDay = CONVERT(VARCHAR(10),CAST(CAST(YEAR(@dDateNow)AS CHAR(4))+'/01/01' AS DATETIME),120)
RETURN @dYearFirstDay END --版权所有:lyxGO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO--取某日期的年最后一天 CREATE FUNCTION dbo.fGetYearLastDay ( @dDateNow SMALLDATETIME ) RETURNS VARCHAR(12) AS BEGIN DECLARE @dYearLastDay VARCHAR(12) SELECT @dYearLastDay = CONVERT(VARCHAR(10),CAST(CAST(YEAR(@dDateNow)AS CHAR(4))+'/12/31' AS DATETIME),120)
RETURN @dYearLastDay END --版权所有:lyx GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
http://technet.microsoft.com/zh-cn/library/ms187928(SQL.90).aspx
1 101 美国 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英国/法国 dd/mm/yy
4 104 德国 dd.mm.yy
5 105 意大利 dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 或 109 (*) 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM(或 PM)
10 110 美国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd
- 13 或 113 (*) 欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 或 120 (*) ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff]
- 21 或 121 (*) ODBC 规范(带毫秒) yyyy-mm-dd hh:mm:ss[.fff]
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(不含空格)
- 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM
- 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM
-----------
2008-09-27(1 row(s) affected)假如只是想将形如YYYY-MM-DD HH:MM:SS.MS 的日期型转换成 YYYY-MM-DD 用该方法即可如果要转换的不确定是从数据库取到的日期用以下两种方法
SELECT CONVERT(varchar(10),GETDATE(),120)SELECT CONVERT(varchar(100), GETDATE(), 23)2008年9月28日测试结果如下:
----------
2008-09-28(1 row(s) affected)
declare @dt datetime
set @dt='2008-09-27 09:22:21.280'
select convert(varchar(10),@dt,120)
select getdate()
-- 返回当前系统日期
--dateadd
-- 返回日相加后的日期 day
select dateadd (dd,5,'1999-9-9')
-- 返回月相加后的日期 month
select dateadd (month,5,'1999-9-9')
-- 返回年相加后的日期 year
select dateadd (yy,5,'1999-9-9')--DateDiff
select datediff(mm,'2003-4-5','2007-6-7')--dateName
--weekday (dw) 日期部分返回星期几(星期天、星期一等)。--DATENAME
--返回代表指定日期的指定日期部分的字符串。--语法
--DATENAME ( datepart , date )--参数
--datepart--是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。--日期部分 缩写
--year yy, yyyy
--quarter qq, q
--month mm, m
--dayofyear dy, y
--day dd, d
--week wk, ww
--weekday dw
--Hour hh
--minute mi, n
--second ss, s
--millisecond ms select datename(dw,'1999-9-9')--datepart
select datepart(day,'1999-9-11')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetMonthDays]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fGetMonthDays]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetMonthFirstDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fGetMonthFirstDay]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetMonthLastDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fGetMonthLastDay]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetYearFirstDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fGetYearFirstDay]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetYearLastDay]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fGetYearLastDay]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--取某日期的月天数
CREATE FUNCTION dbo.fGetMonthDays
(
@dDateNow SMALLDATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @iMonthDays INT
DECLARE @dMonthFirstDay SMALLDATETIME
DECLARE @dMonthLastDay SMALLDATETIME
SELECT @dMonthFirstDay = dbo.fGetMonthFirstDay(@dDateNow),
@dMonthLastDay = dbo.fGetMonthLastDay(@dDateNow)
SELECT @iMonthDays = DATEDIFF (d,@dMonthFirstDay,@dMonthLastDay)+1
RETURN @iMonthDays
END
--版权所有:lyx
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--取某日期的月第一天
CREATE FUNCTION dbo.fGetMonthFirstDay
(
@dDateNow SMALLDATETIME
)
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @dMonthFirstDay VARCHAR(12) SELECT @dMonthFirstDay = CONVERT(VARCHAR(10),CAST(CAST(YEAR(@dDateNow)AS CHAR(4))+'/'+CAST(MONTH(@dDateNow)AS CHAR(2))+'/01' AS DATETIME),120)
RETURN @dMonthFirstDay
END
--版权所有:lyxGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO--取某日期的月最后一天
CREATE FUNCTION dbo.fGetMonthLastDay
(
@dDateNow SMALLDATETIME
)
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @dMonthLastDay VARCHAR(12)
SELECT @dMonthLastDay = CONVERT(VARCHAR(10),DATEADD(d,-1,CAST(CAST(YEAR(@dDateNow)AS CHAR(4))+'/'+CAST(MONTH(DATEADD(m,1, @dDateNow)) AS CHAR(2))+'/01' AS DATETIME)),120)
RETURN @dMonthLastDay
END
--版权所有:lyx
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--取某日期的年第一天
CREATE FUNCTION dbo.fGetYearFirstDay
(
@dDateNow SMALLDATETIME
)
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @dYearFirstDay VARCHAR(12) SELECT @dYearFirstDay = CONVERT(VARCHAR(10),CAST(CAST(YEAR(@dDateNow)AS CHAR(4))+'/01/01' AS DATETIME),120)
RETURN @dYearFirstDay
END
--版权所有:lyxGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO--取某日期的年最后一天
CREATE FUNCTION dbo.fGetYearLastDay
(
@dDateNow SMALLDATETIME
)
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @dYearLastDay VARCHAR(12) SELECT @dYearLastDay = CONVERT(VARCHAR(10),CAST(CAST(YEAR(@dDateNow)AS CHAR(4))+'/12/31' AS DATETIME),120)
RETURN @dYearLastDay
END
--版权所有:lyx
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO