converting datetime in sqlserver like yyyy-mm-dd hh:mi:ss to yyyy-mm-dd 23:59:59

Question

Hi I want to convert the date time like below 2015-05-12 23:59:59. The hours and secs and should come like 12:59:59 this.

Ex: I want to convert today's date like below 2015-08-17 23:59:59.

Edited

for GETDATE() in sql server I will get the datetime like this 2015-08-17 17:10:54.080 this one I want to convert into 2015-08-17 23:59:59.080

Answer 1

Seems this question is not about formatting. So here is a solution to get last timestamps of the day:

To get the last minute of the day:

SELECT dateadd(d, datediff(d, 0, getdate()), cast('23:59:59' as datetime))

Returns:

2015-08-17 23:59:59.000

To get the last possible timestamp of the day:

SELECT dateadd(d, datediff(d, 0, getdate()), cast('23:59:59:997' as datetime))
2015-08-17 23:59:59.997

Answer 2

SQL Server 2005 and Later

SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 

SQL Server 2012 and later versions

SELECT FORMAT(GETDATE() , 'yyyy-MM-dd HH:mm:ss')

Either will return:

2015-08-17 12:42:25