Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL datetime set to midnight

Posted on 2009-07-01
5
Medium Priority
?
688 Views
Last Modified: 2012-05-07
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
Comment
Question by:RossEdwards
  • 3
5 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24755699
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24755701
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24755712
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24755726
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
 
LVL 1

Author Closing Comment

by:RossEdwards
ID: 31598820
Many thanks this worked!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question