sqlserver获取日期时间

发布时间：2020-12-12 16:05:03 所属栏目：MsSql教程来源：网络整理

导读：SELECT convert ( varchar ,getdate(),100) -- mon dd yyyy hh:mmAM (or PM) -- Oct 2 2008 11:01AM SELECT convert ( varchar ,101) -- mm/dd/yyyy - 10/02/2008 SELECT convert ( varchar ,102) -- yyyy.mm.dd -- 2008.10.02 SELECT convert ( varchar ,10

SELECT convert(varchar,getdate(),100)-- mon dd yyyy hh:mmAM (or PM)-- Oct 2 2008 11:01AMSELECT convert(varchar,101)-- mm/dd/yyyy - 10/02/2008SELECT convert(varchar,102)-- yyyy.mm.dd -- 2008.10.02SELECT convert(varchar,103)-- dd/mm/yyyySELECT convert(varchar,104)-- dd.mm.yyyySELECT convert(varchar,105)-- dd-mm-yyyySELECT convert(varchar,106)-- dd mon yyyySELECT convert(varchar,107)-- mon dd,yyyySELECT convert(varchar,108)-- hh:mm:ssSELECT convert(varchar,109)-- mon dd yyyy hh:mm:ss:mmmAM (or PM)-- Oct 2 2008 11:02:44:013AMSELECT convert(varchar,110)-- mm-dd-yyyySELECT convert(varchar,111)-- yyyy/mm/ddSELECT convert(varchar,112)-- yyyymmddSELECT convert(varchar,113)-- dd mon yyyy hh:mm:ss:mmm-- 02 Oct 2008 11:02:07:577SELECT convert(varchar,114)-- hh:mm:ss:mmm(24h)SELECT convert(varchar,120)-- yyyy-mm-dd hh:mm:ss(24h)SELECT convert(varchar,121)-- yyyy-mm-dd hh:mm:ss.mmmSELECT convert(varchar,126)-- yyyy-mm-ddThh:mm:ss.mmm-- 2008-10-02T10:52:47.513-- 利用字符串函数创建不同的日期格式SELECT replace(convert(varchar,111),'/',' ')-- yyyy mm ddSELECT convert(varchar(7),126)-- yyyy-mmSELECT right(convert(varchar,106),8)-- mon yyyygo

--通用的日期转换函数CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME,@FormatMask VARCHAR(32))RETURNS VARCHAR(32)AS

BEGINDECLARE @StringDate VARCHAR(32)SET @StringDate = @FormatMaskIF (CHARINDEX ('YYYY',@StringDate) > 0)SET @StringDate = REPLACE(@StringDate,'YYYY',DATENAME(YY,@Datetime))IF (CHARINDEX ('YY',@StringDate) > 0)SET @StringDate = REPLACE(@StringDate,'YY',RIGHT(DATENAME(YY,@Datetime),2))IF (CHARINDEX ('Month',@StringDate) > 0)SET @StringDate = REPLACE(@StringDate,'Month',DATENAME(MM,@Datetime))IF (CHARINDEX ('MON',@StringDateCOLLATESQL_Latin1_General_CP1_CS_AS)>0)SET @StringDate = REPLACE(@StringDate,'MON',LEFT(UPPER(DATENAME(MM,@Datetime)),3))IF (CHARINDEX ('Mon',@StringDate) > 0)SET @StringDate = REPLACE(@StringDate,'Mon',LEFT(DATENAME(MM,3))IF (CHARINDEX ('MM',@StringDate) > 0)SET @StringDate = REPLACE(@StringDate,'MM',RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM,2))IF (CHARINDEX ('M',@StringDate) > 0)SET @StringDate = REPLACE(@StringDate,'M',CONVERT(VARCHAR,@Datetime)))IF (CHARINDEX ('DD',@StringDate) > 0)SET @StringDate = REPLACE(@StringDate,'DD',right('0'+DATENAME(DD,2))IF (CHARINDEX ('D',@StringDate) > 0)SET @StringDate = REPLACE(@StringDate,'D',DATENAME(DD,@Datetime))RETURN @StringDateENDGO? ? 获取年月，不获取日

1.年月格式为20139，不带“0”
 SELECT CAST(DATEPART(YEAR,m.YMD)AS VARCHAR)+CAST(DATEPART(MONTH,m.YMD) AS VARCHAR) As YMD from M_CALENDAR m

select substring((convert(varchar(7),MC.YMD,112)),1,6) AS?? YMD from M_CALENDAR MCselect convert(nvarchar(6),112)? AS?? YMD from M_CALENDAR MC

2.年月格式为201309，带“0”
 select replace(convert(varchar(7),m.YMD,126),'-','') As YMD from M_CALENDAR m

select CONVERT(NVARCHAR(6),TK.SYSDATE,112) from T_KET TK? 3.获取去年今天的年月日（将今天的日期往前推一年）select convert(varchar,dateadd(year,-1,getdate()),23)? 获取当前的星期select datename(weekday,getdate())? 通过当前日期来获取这个月的天数select day(dateadd(mm,'2013-11-01')-day('2013-11-01'))

以前每次格式化日期都要通过字符串转换函数是转换,比较麻烦,像这样写成函数调用起来就方便多了:

示例:

SELECT dbo.fnFormatDate (getdate(),'MM/DD/YYYY')? --03/06/2011

替换：

SELECT? REPLAC('126ABC544','ABC','aaa');

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!