how to enter manual time stamp in get date ()

Question

how to enter manual time stamp in get date () ?

select conver(varchar(10),getdate(),120)

returns 2010-06-07

now i want to enter my own time stamp in this like 2010-06-07 10.00.00.000

i m using this in

select * from sample table where time_stamp ='2010-06-07 10.00.00.000'

since i m trying to automate this query i need the current date but i need different time stamp can it be done .

Answer 1

SELECT DATEADD(hh, 1, FLOOR(CAST(GETDATE() AS FLOAT)))

Once you have the floor of the date, you can add time to it.

DATEADD(datepart, number, date)

Answer 2

You just want to append a time to your result? Like this?

select convert(varchar(10),getdate(),120) + ' 10.00.00.000'

or if you want to get it back to a DATETIME type:

select convert(datetime,convert(varchar(10),getdate(),120) + ' 10:00')

Answer 3

--SQL Server 2008
DECLARE @MyTime time, @MyDate date

SELECT @MyDate = GETDATE(), @MyTime = '10:00:00'

SELECT CAST(@MyDate AS datetime) + @MyTime

--SQL Server 2005 and before
DECLARE @MyTime datetime, @MyDate datetime

SELECT
   @MyDate = DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())),
   @MyTime = '19000101 10:00:00'

SELECT @MyDate + @MyTime

"zero" date = 01 Jan 1900 in SQL Server