获取存储过程中两个日期时间之间的时差 [英] Get Time Difference between two datetime in stored procedure

问题描述

我想在 stroed 过程中获取两个日期时间之间的时差.然后我需要将该答案转换为 varchar.我不知道如何获得该价值.我是 stroed 程序的新人.我正在使用 sql server 2008

I want to get Get Time Difference between two datetime in stroed procedure. then i need to cast that answer to varchar. i don't know how to get that value. i am a new one for stroed procedure. i am using sql server 2008

declare @tdate1 datetime
declare @date2 datetime
declare @finaltime varchar

set @enddate = '2004-10-18 07:53:35.000'
set @startdate = '2004-10-18 15:28:57.000'

if @startdate >= @enddate
// This is what i want to do..

else

推荐答案

SELECT DATEDIFF(year, @startdate, @enddate)

试试这个，让你朝着正确的方向开始.

try this to start you in the right direction.

year 代表您要返回的度量周期

year represents the period of measure you want to return

这里是 MSDN 文章的链接，可能会有帮助

Here is a link to an MSDN Article that may be helpful

DATEDIFF (Transact-SQL)

DATEDIFF (Transact-SQL)

其他版本

更新时间:2015 年 12 月 2 日

本主题适用于:

是SQL Server(从2008年开始)是Azure SQL数据库是Azure SQL数据仓库是并行数据仓库返回指定开始日期和结束日期之间跨越的指定日期部分边界的计数(有符号整数).有关更大的差异，请参阅 DATEDIFF_BIG (Transact-SQL).有关所有 Transact-SQL 日期和时间数据类型和函数的概述，请参阅日期和时间数据类型和函数 (Transact-SQL).主题链接图标 Transact-SQL 语法约定语法

yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. For larger differences, see DATEDIFF_BIG (Transact-SQL). For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). Topic link icon Transact-SQL Syntax Conventions Syntax

DATEDIFF ( datepart , startdate , enddate )

--Azure SQL 数据仓库和并行数据仓库

-- Azure SQL Data Warehouse and Parallel Data Warehouse

DATEDIFF (datepart ,startdate ,enddate )

参数

日期部分

是 startdate 和 enddate 中指定跨越边界类型的部分.下表列出了所有有效的 datepart 参数.用户定义的变量等效项无效.

Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

日期部分

缩写

year
yy, yyyy
quarter
qq, q
month
mm, m
dayofyear
dy, y
day
dd, d
week
wk, ww
hour
hh
minute
mi, n
second
ss, s
millisecond
ms
microsecond
mcs
nanosecond
ns
startdate

是可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的表达式.日期可以是表达式、列表达式、用户定义的变量或字符串文字.从结束日期减去开始日期.

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.

为避免歧义，请使用四位数年份.有关两位数年份的信息，请参阅配置两位数年份截止服务器配置选项.结束日期

To avoid ambiguity, use four-digit years. For information about two digits years, see Configure the two digit year cutoff Server Configuration Option. enddate

请参阅开始日期.返回类型内部返回值每个日期部分及其缩写都返回相同的值.如果返回值超出 int 范围(-2,147,483,648 到 +2,147,483,647)，则返回错误.对于毫秒，startdate 和 enddate 之间的最大差异为 24 天 20 小时 31 分 23.647 秒.其次，最大的差异是 68 年.如果 startdate 和 enddate 都只分配了一个时间值并且 datepart 不是时间 datepart，则返回 0.计算返回值时不使用 startdate 或 endate 的时区偏移组件.由于 smalldatetime 仅精确到分钟，因此当开始日期或结束日期使用 smalldatetime 值时，返回值中的秒和毫秒始终设置为 0.如果仅将时间值分配给日期数据类型的变量，则缺失日期部分的值将设置为默认值:1900-01-01.如果仅将日期值分配给时间或日期数据类型的变量，则缺失时间部分的值将设置为默认值:00:00:00.如果 startdate 或 enddate 只有时间部分，而另一个只有日期部分，则缺少的时间和日期部分将设置为默认值.如果 startdate 和 enddate 属于不同的日期数据类型，并且一个的时间部分或小数秒精度比另一个多，则另一个的缺失部分设置为 0.日期部分边界以下语句具有相同的开始日期和相同的结束日期.这些日期是相邻的，时间相差 0.0000001 秒.每条语句中 startdate 和 endate 之间的差异跨越其日期部分的一个日历或时间边界.每条语句返回 1.如果此示例使用不同的年份，并且 startdate 和 endate 都在同一日历周内，则 week 的返回值将为 0.

See startdate. Return Type int Return Value Each datepart and its abbreviations return the same value. If the return value is out of range for int (-2,147,483,648 to +2,147,483,647), an error is returned. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years. If startdate and enddate are both assigned only a time value and the datepart is not a time datepart, 0 is returned. A time zone offset component of startdate or endate is not used in calculating the return value. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used for startdate or enddate, seconds and milliseconds are always set to 0 in the return value. If only a time value is assigned to a variable of a date data type, the value of the missing date part is set to the default value: 1900-01-01. If only a date value is assigned to a variable of a time or date data type, the value of the missing time part is set to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a date part, the missing time and date parts are set to the default values. If startdate and enddate are of different date data types and one has more time parts or fractional seconds precision than the other, the missing parts of the other are set to 0. datepart Boundaries The following statements have the same startdate and the same endate. Those dates are adjacent and differ in time by .0000001 second. The difference between the startdate and endate in each statement crosses one calendar or time boundary of its datepart. Each statement returns 1. If different years are used for this example and if both startdate and endate are in the same calendar week, the return value for week would be 0.

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999'
, '2006-01-01 00:00:00.0000000');

备注DATEDIFF 可用于选择列表、WHERE、HAVING、GROUP BY 和 ORDER BY 子句.DATEDIFF 将字符串文字隐式转换为 datetime2 类型.这意味着当日期作为字符串传递时， DATEDIFF 不支持 YDM 格式.您必须将字符串显式转换为 datetime 或 smalldatetime 类型才能使用 YDM 格式.指定 SET DATEFIRST 对 DATEDIFF 没有影响.DATEDIFF 始终使用星期日作为一周的第一天，以确保该函数具有确定性.例子以下示例使用不同类型的表达式作为 startdate 和 enddate 参数的参数.A. 指定开始日期和结束日期的列以下示例计算表格中两列日期之间跨越的天数.

Remarks DATEDIFF can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses. DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format. Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic. Examples The following examples use different types of expressions as arguments for the startdate and enddate parameters. A. Specifying columns for startdate and enddate The following example calculates the number of day boundaries that are crossed between dates in two columns in a table.

CREATE TABLE dbo.Duration
    (
    startDate datetime2
    ,endDate datetime2
    );
INSERT INTO dbo.Duration(startDate,endDate)
    VALUES('2007-05-06 12:10:09','2007-05-07 12:10:09');
SELECT DATEDIFF(day,startDate,endDate) AS 'Duration'
FROM dbo.Duration;

-- 返回:1B. 为 startdate 和 enddate 指定用户定义的变量以下示例使用用户定义的变量作为 startdate 和 enddate 的参数.

-- Returns: 1 B. Specifying user-defined variables for startdate and enddate The following example uses user-defined variables as arguments for startdate and enddate.

DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate datetime2 = '2007-05-04 12:10:09.3312722'; 
SELECT DATEDIFF(day, @startdate, @enddate);

C.为 startdate 和 enddate 指定标量系统函数以下示例使用标量系统函数作为 startdate 和 enddate 的参数.

C. Specifying scalar system functions for startdate and enddate The following example uses scalar system functions as arguments for startdate and enddate.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());

D.为 startdate 和 enddate 指定标量子查询和标量函数以下示例使用标量子查询和标量函数作为 startdate 和 enddate 的参数.使用 AdventureWorks2012；去

D. Specifying scalar subqueries and scalar functions for startdate and enddate The following example uses scalar subqueries and scalar functions as arguments for startdate and enddate. USE AdventureWorks2012; GO

SELECT DATEDIFF(day,(SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),
    (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));

E.为 startdate 和 enddate 指定常量以下示例使用字符常量作为 startdate 和 enddate 的参数.

E. Specifying constants for startdate and enddate The following example uses character constants as arguments for startdate and enddate.

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635'
    , '2007-05-08 09:53:01.0376635');

F.为 enddate 指定数值表达式和标量系统函数以下示例使用数值表达式 (GETDATE ()+ 1) 和标量系统函数 GETDATE 和 SYSDATETIME 作为 enddate 的参数.

F. Specifying numeric expressions and scalar system functions for enddate The following example uses a numeric expression, (GETDATE ()+ 1), and scalar system functions, GETDATE and SYSDATETIME, as arguments for enddate.

USE AdventureWorks2012;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE()+ 1) 
    AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
USE AdventureWorks2012;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', DATEADD(day,1,SYSDATETIME())) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
G. Specifying ranking functions for startdate
The following example uses a ranking function as an argument for startdate.
USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName
    ,DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY 
        a.PostalCode),SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson s 
    INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

H.为 startdate 指定聚合窗口函数以下示例使用聚合窗口函数作为 startdate 的参数.

H. Specifying an aggregate window function for startdate The following example uses an aggregate window function as an argument for startdate.

USE AdventureWorks2012;
GO
SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty,soh.OrderDate
    ,DATEDIFF(day,MIN(soh.OrderDate) 
        OVER(PARTITION BY soh.SalesOrderID),SYSDATETIME() ) AS 'Total'
FROM Sales.SalesOrderDetail sod
    INNER JOIN Sales.SalesOrderHeader soh
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN(43659,58918);
GO

示例:Azure SQL 数据仓库公共预览版和并行数据仓库以下示例使用不同类型的表达式作为 startdate 和 enddate 参数的参数.I. 指定开始日期和结束日期的列以下示例计算表格中两列日期之间跨越的天数.

Examples: Azure SQL Data Warehouse Public Preview and Parallel Data Warehouse The following examples use different types of expressions as arguments for the startdate and enddate parameters. I. Specifying columns for startdate and enddate The following example calculates the number of day boundaries that are crossed between dates in two columns in a table.

CREATE TABLE dbo.Duration (
    startDate datetime2
    ,endDate datetime2
    );
INSERT INTO dbo.Duration(startDate,endDate)
    VALUES('2007-05-06 12:10:09','2007-05-07 12:10:09');
SELECT TOP(1) DATEDIFF(day,startDate,endDate) AS Duration
FROM dbo.Duration;

-- 返回:1J. 为 startdate 和 enddate 指定标量子查询和标量函数以下示例使用标量子查询和标量函数作为 startdate 和 enddate 的参数.-- 使用 AdventureWorks

-- Returns: 1 J. Specifying scalar subqueries and scalar functions for startdate and enddate The following example uses scalar subqueries and scalar functions as arguments for startdate and enddate. -- Uses AdventureWorks

SELECT TOP(1) DATEDIFF(day,(SELECT MIN(HireDate) FROM dbo.DimEmployee),
    (SELECT MAX(HireDate) FROM dbo.DimEmployee)) 
FROM dbo.DimEmployee;

K.为 startdate 和 enddate 指定常量以下示例使用字符常量作为 startdate 和 enddate 的参数.

K. Specifying constants for startdate and enddate The following example uses character constants as arguments for startdate and enddate.

-- Uses AdventureWorks

SELECT TOP(1) DATEDIFF(day, '2007-05-07 09:53:01.0376635'
    , '2007-05-08 09:53:01.0376635') FROM DimCustomer;

L.指定开始日期的排名函数以下示例使用排名函数作为 startdate 的参数.-- 使用 AdventureWorks

L. Specifying ranking functions for startdate The following example uses a ranking function as an argument for startdate. -- Uses AdventureWorks

SELECT FirstName, LastName
,DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY 
        DepartmentName),SYSDATETIME()) AS RowNumber
FROM dbo.DimEmployee;

M.为 startdate 指定聚合窗口函数以下示例使用聚合窗口函数作为 startdate 的参数.-- 使用 AdventureWorks

M. Specifying an aggregate window function for startdate The following example uses an aggregate window function as an argument for startdate. -- Uses AdventureWorks

SELECT FirstName, LastName, DepartmentName
    ,DATEDIFF(year,MAX(HireDate)
             OVER (PARTITION BY DepartmentName),SYSDATETIME()) AS SomeValue
FROM dbo.DimEmployee

这篇关于获取存储过程中两个日期时间之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！