当前日期和结束时间在sql

I have the query like this:

我有这样的查询:

select cast(convert(varchar(10), getdate(), 110) as datetime)

This is returning out put like this:

这是这样返回的:

currentdate
-----------------------
2013-07-28 00:00:00.000

but I want to get current date with ending time. My expected output is like this:

但是我想要得到当前日期和结束时间。我的期望输出是这样的:

currentdate
-----------------------
2013-07-28 23:59:59

4 个解决方案

#1

Well, in that case, you need to give your varchar that you're converting to more characters!

在这种情况下，你需要给你的varchar你要转换成更多的字符!

select cast(convert(varchar(30), getdate(), 110) as datetime)
                            **

And the question really is: why are you converting the output from GETDATE() into a Varchar and then back to a DATETIME in the first place??

真正的问题是:为什么要首先将GETDATE()的输出转换为Varchar，然后再转换回DATETIME ?

Can't you just use

你不能用吗

SELECT GETDATE()

and that's all?

这是所有吗?

Update: OK, so you want to get the current day, but the fixed time of 23:59:59?

更新:好的，你想要得到当前的日期，但是时间是23:59:59的固定时间?

Try this:

试试这个:

SELECT
   CAST(CONVERT(VARCHAR(10), GETDATE(), 110) + ' 23:59:59' AS DATETIME)

#2

If you want to return the current date with the end time, then you will have to append the end time manually:

如果您想返回当前日期和结束时间，那么您将不得不手动追加结束时间:

select cast(convert(varchar(10), getdate(), 110) + ' 23:59:59.997' as datetime)

选择cast(转换为varchar(10)、getdate()、110)+ ' 23:59:59.997'作为datetime)

#3

Either of the below will do it for display purposes;

以下任何一种都将用于显示目的;

SELECT DATEADD(s, -1, DATEADD(day, 1, 
          CONVERT(DATETIME, CONVERT(DATE, GETDATE()))));

...or...

…或…

SELECT DATEADD(s, -1, DATEADD(day, 1,
          DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)));

You could also just set a fixed time of 23:59:59, but you'd end up with an invalid time at leap second adjustments (which may or may not be ok)

你也可以设置一个固定的时间23:59:59，但是在闰秒调整时你会得到一个无效的时间(这可能是正确的，也可能不是正确的)

If you want to use it for comparison purposes, it's better to just use "less than the next date".

如果你想用它来做比较，最好是用“比下次约会少”。

#4

Just for display (without milliseconds)

只是为了显示(没有毫秒)

SELECT
    CONVERT(VARCHAR(10), GETDATE(), 110) + ' 23:59:59'

#1