Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

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).

0
Ross Edwards
Asked:
Ross Edwards
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
1. This should help

update ur_table
set ur_datetime_col = dateadd(d, datediff(d, 0, ur_datetime_col), 0)

2. This should help

select * from ur_table
where ur_datetime_col between ur_datetime_col = dateadd(d, datediff(d, 0, ur_datetime_col), 0)
and ur_datetime_col = dateadd( ms, -2 , dateadd(d, datediff(d, 0, ur_datetime_col), 1))
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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))
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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()
0
 
Ross EdwardsTechnical DirectorAuthor Commented:
Many thanks this worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now