使用日期和時間資料

DECLARE @Today date = '12/1/2003';
DECLARE @MyTime time = '12:30:22';
DECLARE @MyDatetimeoffset datetimeoffset = '12/1/2003 12:30:22 -5:00';
SET LANGUAGE Italian
    -- Returns: Changed language setting to Italiano.
SELECT DATENAME(month, @Today) AS 'Month Name';
    -- Returns: dicembre
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
    -- Returns: dic  1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
    -- Returns: 12

SET LANGUAGE us_english;
    -- Returns: Changed language setting to us_english.
SELECT DATENAME(month, @Today) AS 'Month Name';
    -- Returns: December
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
    -- Returns: Dec  1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
    -- Returns: 12
GO
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
    -- Returns: 1998-12-31
GO
-- Set date format to year, day, month. 
SET DATEFORMAT ydm;
GO
DECLARE @datevar datetimeoffset = '1998/31/12 12:30:22 -05:00';
SELECT @datevar AS DateVar;
    -- Returns: Msg 241, Conversion failed when converting 
    -- date and/or time from character string.
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
    -- Returns: 1998-12-31
GO

SELECT CAST('01/01/2000 14:30' AS datetime2)
    --Returns: 2000-01-01 14:30:00.0000000
SELECT CAST('01/01/2000 14:30:20:999' AS datetime2)
    -- Returns: 2000-01-01 14:30:20.9990000
SELECT CAST('01/01/2000 14:30:20.9' AS datetime2)
    -- Returns: 2000-01-01 14:30:20.9000000
SELECT CAST('01/01/2000 4am' AS datetime2)
    -- Returns: 2000-01-01 04:00:00.0000000
SELECT CAST('01/01/2000 4 PM' AS datetime2)
    -- Returns: 2000-01-01 16:00:00.0000000
SELECT CAST('01/01/2000 04:30:20:500AM' AS datetime2)
    -- Returns: 2000-01-01 04:30:20.5000000
SELECT CAST('01/01/2000 04:30:20:500 AM' AS datetime2)
    -- Returns: 2000-01-01 04:30:20.5000000
GO

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
        'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
        'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 
        'datetimeoffset';

SELECT SYSDATETIME() AS 'SYSDATETIME'

--Results
--SYSDATETIME
--2007-10-22 14:10:41.7984554
--(1 row(s) affected)

SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'

--Results
--SYSDATETIMEOFFSET
--2007-10-22 14:11:34.2607172 -0
--(1 row(s) affected)

SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'

--Results
--SYSUTCDATETIME
--2007-10-22 21:12:11.7069470
--(1 row(s) affected)

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'

--Results
--CURRENT_TIMESTAMP
-------------------------
--2007-10-22 14:12:33.320
--(1 row(s) affected)

SELECT GETDATE() AS 'GETDATE'

--Results
--GETDATE
--2007-10-22 14:13:57.943
--(1 row(s) affected)

SELECT GETUTCDATE() AS 'GETUTCDATE'

--Results
--GETUTCDATE
--2007-10-22 21:14:35.657
--(1 row(s) affected)

-- Create a table that contains with the following dates:
--     The last date-time in 2005-04-06, '2005-04-06 23:59:59.9999999'
--     The first date-time in 2005-04-07, '2005-04-07 00:00:00.0000000'
--     The last date-time in 2005-04-07, '2005-04-07 23:59:59.9999999'
--     The first date-time in 2005-04-08, '2005-04-08 00:00:00.0000000'
CREATE TABLE #Search
    (
    MyDate datetime2
    );
INSERT INTO #Search(MyDate)VALUES('2005-04-06 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 00:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-08 00:00:00.0000000');

-- The following four SELECT statements show different ways to find
-- only the two rows that contain 2005-04-07 dates.
--Use CONVERT.
SELECT MyDate 
FROM #Search 
WHERE CONVERT(date,MyDate) = '2005-04-07';

--Use >= and <=.
SELECT MyDate 
FROM #Search 
WHERE MyDate >= '2005-04-07 00:00:00.0000000' 
    AND MyDate <= '2005-04-07 23:59:59.9999999';

--Use > and <.
SELECT MyDate
FROM #Search
WHERE MyDate > '2005-04-06 23:59:59.9999999'
    AND MyDate < '2005-04-08 00:00:00.0000000';

--Use BETWEEN AND.
SELECT MyDate
FROM #Search
WHERE MyDate BETWEEN '2005-04-07 00:00:00.0000000'
    AND '2005-04-07 23:59:59.9999999';
DROP TABLE #Search
GO

-- Create a table called Search and insert
-- five different time values for the same
-- date.

CREATE TABLE #Search
    (
    MyDate datetime2
    );

INSERT INTO #Search(MyDate)VALUES('2005-04-06 08:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 16:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 17:00:00.0000000');

-- The following SELECT statements show different ways
-- to search for dates that have time values to find a
-- time range.

--Using CONVERT with time (0) to ignore fractional seconds
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(0),MyDate) = '09:00:00';

--Returns two rows (ignores fractional seconds):
--2005-04-06 08:59:59.9999999
--2005-04-06 09:00:00.0000000

--Using CONVERT with time (7) to include fractional seconds
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(7),MyDate) = '09:00:00';

--Returns one row (matches fractional seconds):
--2005-04-06 09:00:00.0000000


--Each of the SELECT statements below use CONVERT
--to find all times in an eight-hour period.

--Use CONVERT with >= and <=.
SELECT MyDate 
FROM #Search 
WHERE CONVERT(time(7),MyDate) >=  '09:00:00.0000000'
    AND CONVERT(time(7),MyDate) <= '16:59:59.9999999'

--Use CONVERT with > and <.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) > '08:59:59.9999999'
    AND CONVERT(time(7),MyDate) < '17:00:00.0000000';

--Use CONVERT with BETWEEN AND
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) BETWEEN '09:00:00.0000000'
    AND '16:59:59.9999999';
DROP TABLE #Search
GO

-- When several months, quarters, or years are added to the base
-- year,1900-01-01, the returned day is the first day of a month.
-- To calculate the last day of the current month, you need to
--
-- 1. Find the difference in months between today and the base
--    year (0 ='1900-01-01'). This is accomplished by
--
-- DATEDIFF(month, 0, SYSDATETIME())
-- 2. Add that number of months plus 1 to the base year (0)
--    to obtain the first day of the next month.
--    DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0)
-- 3. Subtract 1 day.
--
--Find the first day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()), 0);

--Find the last day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0) - 1;

-- Find the first day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, 0, SYSDATETIME()), 0);

-- Find the last day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, SYSDATETIME()), -1);

-- Find the first day of the current year.
SELECT DATEADD(year, DATEDIFF(year, 0, SYSDATETIME()), 0);

-- Find the last day of the current year.
SELECT DATEADD(year, DATEDIFF(year, -1, SYSDATETIME()), -1);

USE tempdb
GO
CREATE FUNCTION UserDateadd
    (
    @datepart nvarchar(11)
    ,@number int 
    ,@date datetime2
    )
RETURNS datetime2
AS
BEGIN
    DECLARE @Return datetime2

    SELECT @Return = CASE @datepart
        WHEN 'year' THEN DATEADD(year,@number,@date) 
        WHEN 'yy' THEN DATEADD(year,@number,@date) 
        WHEN 'yyyy' THEN DATEADD(year,@number,@date) 
        WHEN 'quarter' THEN DATEADD(quarter,@number,@date) 
        WHEN 'qq' THEN DATEADD(quarter,@number,@date) 
        WHEN 'month' THEN DATEADD(month,@number,@date) 
        WHEN 'mm' THEN DATEADD(month,@number,@date) 
        WHEN 'm' THEN DATEADD(month,@number,@date) 
        WHEN 'dayofyear' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'dy' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'y' THEN DATEADD(dayofyear,@number,@date) 
        WHEN 'day' THEN DATEADD(day,@number,@date) 
        WHEN 'dd' THEN DATEADD(day,@number,@date) 
        WHEN 'd' THEN DATEADD(day,@number,@date) 
        WHEN 'week' THEN DATEADD(week,@number,@date) 
        WHEN 'wk' THEN DATEADD(week,@number,@date) 
        WHEN 'ww' THEN DATEADD(week,@number,@date) 
        WHEN 'weekday' THEN DATEADD(weekday,@number,@date) 
        WHEN 'wk' THEN DATEADD(weekday,@number,@date) 
        WHEN 'w' THEN DATEADD(weekday,@number,@date) 
        WHEN 'hour' THEN DATEADD(hour,@number,@date) 
        WHEN 'hh' THEN DATEADD(hour,@number,@date) 
        WHEN 'minute' THEN DATEADD(minute,@number,@date)
        WHEN 'mi' THEN DATEADD(minute,@number,@date) 
        WHEN 'n' THEN DATEADD(minute,@number,@date)  
        WHEN 'second' THEN DATEADD(second,@number,@date) 
        WHEN 'ss' THEN DATEADD(second,@number,@date)
        WHEN 's' THEN DATEADD(second,@number,@date)
        WHEN 'millisecond' THEN DATEADD(millisecond,@number,@date) 
        WHEN 'ms' THEN DATEADD(millisecond,@number,@date) 
        WHEN 'microsecond' THEN DATEADD(microsecond,@number,@date)
        WHEN 'mcs' THEN DATEADD(microsecond,@number,@date)
        WHEN 'nanosecond' THEN DATEADD(nanosecond,@number,@date)
        WHEN 'ns' THEN DATEADD(nanosecond,@number,@date)
    END
    return @Return
END
GO

SELECT GetDate()
SELECT dbo.UserDateadd('year', 1, GetDate())
GO