Format dates:
SELECT CONVERT(CHAR(10), GETDATE(), 111), -- yyyy/mm/dd 2012/06/19 CONVERT(CHAR(10), GETDATE(), 1), -- mm/dd/yy 06/19/12 CONVERT(CHAR(10), GETDATE(), 101), -- mm/dd/yyyy 06/19/2012 CONVERT(CHAR(19), GETDATE(), 120), -- yyyy-mm-dd 2012-06-19 12:32:51 CONVERT(CHAR(10), GETDATE(), 107) -- mmm dd, yyyy Jun 19, 20
Minimum and maximum date in SQL Server:
Minimum: 1/1/1753 Maximum: 12/31/9999
Cast a string to a date:
CAST('20050312' AS DATETIME) CAST('17530101' AS DATETIME) -- min SQL date CAST('99991231' AS DATETIME) -- max SQL date
Examples of built-in functions involving DATETIME:
DECLARE @DATE DATETIME SET @DATE = CAST('20120314' AS DATETIME) -- 03/14/2012 -- Add 6 days SELECT DATEADD(d, 6, @DATE) -- 03/20/2012 -- Get the week part SELECT DATEPART(wk, @DATE) -- 12 -- Subtract one day SELECT DATEADD(DAY, -1, @DATE) -- Get date parts SELECT YEAR(@DATE) SELECT MONTH(@DATE) SELECT DAY(@DATE) SELECT DATEPART(HOUR, @DATE) SELECT DATEPART(MINUTE, @DATE)
Create DATETIME from year, month, and day parts:
DECLARE @DAY INT, @MONTH INT, @YEAR INT SET @DAY = 18 SET @MONTH = 7 SET @YEAR = 2014 SELECT CAST(RTRIM(@YEAR*10000 + @MONTH*100 + @DAY) AS DATETIME) -- 2014-07-18 00:00:00.000 -- As a function: CREATE FUNCTION fn_GetDate(@DAY INT, @MONTH INT, @YEAR INT) RETURNS DATETIME AS BEGIN RETURN CAST(RTRIM(@YEAR*10000 + @MONTH*100 + @DAY) AS DATETIME) END GO
Drop the time component from a DateTime:
CREATE FUNCTION fn_GetDateOnly(@DateTime DATETIME) RETURNS CHAR(10) AS BEGIN RETURN CONVERT(CHAR(10), @DateTime, 101) END GO SELECT dbo.fn_GetDateOnly(GETDATE()) -- result: 12/17/2012