Getdate tsql question

Question

How to write TSQL so that it calculates the following:

• current date and average night, such as 2010-12-01 00: 00: 00.000
• current date and 6 pm, 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 

Answer 2

• dateadd(dd,datediff(dd,0, getDate()),0)
• dateadd(hh,18 + (24 *datediff(dd,0, getDate())),0)