asked on

SQL datetime set to midnight

Hi all

There are two elements to this question, but both simple answers.

1) I have a table of datetimes for which the time component is whenever the record was created. I need a SQL update query to set them all to midnight.

ie to convert 01/05/2009 21:23:22 to 01/05/2009 00:00:00

2) I am running a query which gets all matching records between @fromdate and @todate. Where these are string parameters from user input.

How can I add a time to the parameter like this:

@fromdate must be @fromdate 00:00:00 (ie midnight)

@todate must be @todate 23:59:59 (ie last second of the day).

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

UPDATE urTable
SET urDateColumn = CONVERT(Varchar, urdatecolumn, 112)
WHERE urDateColumn >=@fromdate AND urDateColumn < @todate -1

UPDATE urTable
SET urDateColumn = DATEADD(second, -1, @Todate )
WHERE urDateColumn >= @todate -1 AND urDateColumn < @Todate

Raja Jegan R

Minor mistake for 2:

select * from ur_table
where ur_datetime_col between dateadd(d, datediff(d, 0, ur_datetime_col), 0)
and dateadd( ms, -2 , dateadd(d, datediff(d, 0, ur_datetime_col), 1))

Raja Jegan R

For 2, if you want it for the current day, then below should help

select * from ur_table
where ur_datetime_col between dateadd(d, datediff(d, 0, getdate()), 0)
and dateadd( ms, -2 , dateadd(d, datediff(d, 0, getdate()), 1))

or Otherwise subtract the days from getdate()

Ross Edwards

ASKER

Many thanks this worked!