Solved

SQL datetime set to midnight

Posted on 2009-07-01
5
661 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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Currency in SQL? 2 43
Where on a calculated field 1 30
sql query help 15 50
Getting "Invalid Operation" Error when opening a Recordset 10 32
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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