getdate()日期變化 on sqlserver

getdate()日期變化 on sqlserver

Using SQL Server 2008

-- remove the time
SELECT CAST(GETDATE() AS date) -- 2009-07-12
-- remove the date
SELECT CAST(GETDATE() AS time) -- 08:46:25.8130000


Cast Date with No Time Using Convert

-- Month first
SELECT CONVERT(varchar(12),GETDATE(), 101) -- 06/29/2009
SELECT CONVERT(varchar(12),GETDATE(), 110) -- 06-29-2009
SELECT CONVERT(varchar(12),GETDATE(), 100) -- Jun 29 2009
SELECT CONVERT(varchar(12),GETDATE(), 107) -- Jun 29, 2009

-- Year first
SELECT CONVERT(varchar(12),GETDATE(), 102) -- 2009.06.29
SELECT CONVERT(varchar(12),GETDATE(), 111) -- 2009/06/29
SELECT CONVERT(varchar(12),GETDATE(), 112) -- 20090629

-- Day first
SELECT CONVERT(varchar(12),GETDATE(), 103) -- 29/06/2009
SELECT CONVERT(varchar(12),GETDATE(), 105) -- 29-06-2009
SELECT CONVERT(varchar(12),GETDATE(), 104) -- 29.06.2009
SELECT CONVERT(varchar(12),GETDATE(), 106) -- 29 Jun 2009

-- Time only
SELECT CONVERT(varchar(12),GETDATE(), 108) -- 07:26:16
SELECT CONVERT(varchar(12),GETDATE(), 114) -- 07:27:11:203


Roll Your Own Date

SELECT DATENAME(MONTH, GETDATE()) -- June
SELECT DATENAME(DAY,GETDATE()) -- 29
SELECT DATENAME(YEAR, GETDATE()) -- 2009

-- Concatente values
-- June.29.2009
SELECT DATENAME(MONTH, GETDATE()) + '.' + DATENAME(DAY,GETDATE()) + '.' + DATENAME(YEAR, GETDATE())



Cast Date With No Time Using Floor

-- Get the current day with no time
-- 2009-06-29 00:00:00.000
SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)

-- Get the next day
-- 2009-06-30 00:00:00.000
SELECT CAST(CEILING (CAST(GETDATE() AS float)) AS datetime)