Changing Time in a Datetime Field

Question

Post reply

Changing Time in a Datetime Field

Roger Abram

Hall of Fame

Points: 3683
More actions
August 25, 2009 at 6:59 am

#135689

I want to reset the time in some records I imported into the database so that there really is no time information so that the new records are consistent with what is already in the database.
An example:
I need a script to update:
2006-09-26 15:13:52.000
So that it is:
2006-09-26 00:00:00.000
Been playing around with syntax but can't seem to get it just right.
Thank you for any assistance.
Rog

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

update mytable set datetimefld=convert(varchar, datetimefld,101)

Try with that code.

p.s. - i didnt test it.

Pradeep Singh

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

Try this, it's what I always use, what it does is find the number of days since the start of datetime, (somewhere around 1900ish), and add that number of days to that date, that way, you get rid of the time.

declare @date datetime

set @Date = '2006-09-26 15:13:52.000'

select dateadd(dd,0,datediff(dd,0,@date))

Cheers,

J-F

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

FYI , this is the best resource on dates in sqlserver

http://www.karaszi.com/SQLServer/info_datetime.asp

Clear Sky SQL
My Blog[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

declare @yourdate datetime

set @yourdate = GETDATE()

select @yourdate

-- here is the syntax for the most efficient execution (avoids converting to char and back)

select DATEADD(DAY,DATEDIFF(day,0,@yourdate),0)

-- this also works, but eats a lot more cpu cycles

select cast(left(@yourdate,11) as datetime)

Dang it, you typed quicker J-F..

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Roger Abram Hall of Fame Points: 3683 More actions · Answer 5

This worked fine and ran quick... just had 5,000 records to update. Thank you everyone!

Roger

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 6

Bob Hovious (8/25/2009)
declare @yourdate datetime
Dang it, you typed quicker J-F..

Happy to be quicker for the first time Bob, 😉

Good thing it's not a tally solution, because you would've beaten the hell out me!

Cheers,

J-F