我想获取特定日期的数据 [英] I want to get data for particular date

问题描述

我想从sql server数据库中获取特定日期的数据.

例如

今天是2008-04-09；无论何时
开始日期应为2008-04-09 00:00:01
结束日期应为2008-04-09 23:59:59


有人可以给我提示如何在SQL Server中设置相同的开始日期和结束日期.

I want to get data for particular date from sql server database.

Eg.

Today is 2008-04-09; whatever time
Startdate should be 2008-04-09 00:00:01
Enddate should be 2008-04-09 23:59:59


Can someone give me tips how i can set Start Date and End Date for the same in SQL Server.

推荐答案

尝试:

SELECT	DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101'),
	DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '23:59:59')

有一些做到这一点的方法.我的建议是(如果使用.Net)将创建一个将日期固定为特定时间点的扩展方法或函数.

因为我也不知道您指的是什么语言，所以这里有一些建议.如果这不正确，请改善问题.:

There are a few ways to do this. My advice is to (if using .Net) would be to create say an Extension method or function that fixes the date to a specific point in time.

Because I do not know what language you are referring too here are some suggestions. If this is not correct please Improve question.:

Imports System.Runtime.CompilerServices

Namespace Extensibility
    Public Module DateAndTimesExt
        <extension()> _
        Public Function ToStartOfDay(ByVal input As DateTime) As DateTime
            Return CDate(String.Concat(input.ToShortDateString, " 00:00:00"))
        End Function

        <extension()> _
        Public Function ToEndOfDay(ByVal input As DateTime) As DateTime
            Return CDate(String.Concat(input.ToShortDateString, " 23:59:59"))
        End Function
    End Module
End Namespace

在代码中使用代码时，您可以在任何日期时间执行< datetime> .ToStartOfDay等

如果您想在sql中修复日期，则可以使用Scalar函数，例如:

When you use in code you could on any datetime do <datetime>.ToStartOfDay etc

If you want to fix a date in sql I would use a Scalar function like:

-- Description:	Calculates end of day returns 01/01/2011 00:00:00
CREATE FUNCTION [dbo].[fn_GetStartOfDay]
(
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	Declare @NewDate datetime
	
	select @NewDate = (CAST(FLOOR(CAST(@Date AS FLOAT))AS DATETIME))

	RETURN @NewDate

END

-- Description:	Calculates end of day returns 01/01/2011 23:59:59
CREATE FUNCTION [dbo].[fn_GetEndOfDay]
(
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	Declare @NewDate datetime
	
	if @Date = (select dbo.fn_GetStartOfDay(@Date))
	begin
		select @Date = dateadd(minute, 1, @Date)
	end

	select @NewDate = dateadd(s, -1, (CAST(ceiling(CAST(@Date AS FLOAT)) AS DATETIME)))

	RETURN @NewDate

END

在sql中，您将引用以下函数:

In sql you would reference the function like:

Select dbo.fn_GetStartOfDay(UpdatedDate) as UpdatedDate from Table

这篇关于我想获取特定日期的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！