getdate tsql question

Question

How do I write TSQL that it calculates the following:

1. the current date and mid night such as 2010-12-01 00:00:00.000
2. the current date and 6pm such as 2010-12-01 18:00:00.000

Thanks..

Answer 1

You can try something like the following

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS DateNoTime, 
    DATEADD(hh, 18, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS DateNewTime