Update field in dimDate table

Question

Post reply

Update field in dimDate table

jbalbo

SSCertifiable

Points: 7583
More actions
March 8, 2013 at 1:47 pm

#275091

Hi
I have a dimDate table that I need to update a field
DateFull = '1900-01-01 00:00:00.000'
= '1900-01-02 00:00:00.000'
etc.....
I need to change to = '1900-01-02 23:59:59.000'
Thanks
Joe

Viewing 9 posts - 1 through 8 (of 8 total)

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

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

So you're just looking to update each date to the end of the day?

Will this help you get started?

declare @datefield datetime

set @datefield = GETDATE()

select @datefield originalDate, dateadd(ms,-3,cast((dateadd(d,1,cast(@datefield as DATE)) )as datetime)) updatedDate

Original Date:2013-03-08 15:10:04.677Updated Date:2013-03-08 23:59:59.997

Sean Lange SSC Guru Points: 286601 More actions · Answer 2

Erin Ramsay (3/8/2013)
So you're just looking to update each date to the end of the day?
Will this help you get started?
declare @datefield datetime
set @datefield = GETDATE()
select @datefield originalDate, dateadd(ms,-3,cast((dateadd(d,1,cast(@datefield as DATE)) )as datetime)) updatedDate
Original Date:2013-03-08 15:10:04.677Updated Date:2013-03-08 23:59:59.997

No need to do all the casting.

select @datefield originalDate, dateadd(ms,-3, dateadd(d,1, @datefield)) updatedDate

Once I removed all the extra casts this look remarkably like the version I was about to post.

dateadd(millisecond, -1, dateadd(day, 1, DateFull))

I like to use the whole part name because I can't every seem to remember many of them.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Sean Lange (3/8/2013)
Erin Ramsay (3/8/2013)
So you're just looking to update each date to the end of the day?
Will this help you get started?
declare @datefield datetime
set @datefield = GETDATE()
select @datefield originalDate, dateadd(ms,-3,cast((dateadd(d,1,cast(@datefield as DATE)) )as datetime)) updatedDate
Original Date:2013-03-08 15:10:04.677Updated Date:2013-03-08 23:59:59.997
No need to do all the casting.
select @datefield originalDate, dateadd(ms,-3, dateadd(d,1, @datefield)) updatedDate
Once I removed all the extra casts this look remarkably like the version I was about to post.
dateadd(millisecond, -1, dateadd(day, 1, DateFull))
I like to use the whole part name because I can't every seem to remember many of them.

Look carefully with eye at the results of the following:

declare @datefield datetime = '1900-01-01 00:00:00.000';

select @datefield originalDate, dateadd(ms,-3, dateadd(d,1, @datefield)) updatedDate, dateadd(millisecond, -1, dateadd(day, 1, @datefield))

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

Thanks, Sean.

I did all the casting to account for a time like 2013-03-08 15:18:50.807 and you add a day you get 2013-03-09 15:18:50.807.

If you take way 1-3 milliseconds from that you're not going to get the results he's looking for. I just wanted to make sure that if he gave me a date for any time during that day it would increment properly to the end of that day.

Erin

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 5

Erin Ramsay

SSCrazy Eights

Points: 9728

March 8, 2013 at 3:23 pm

#1595148

That's why I used -3 milliseconds, Lynn.

jbalbo SSCertifiable Points: 7583 More actions · Answer 6

Hi

I just did

SET datefull = dateadd(ms,-3,cast((dateadd(d,1,cast(datefull as DATE)) )as datetime))

worked great

Thanks for all the help

Now I just have to read up on what it all does

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Erin Ramsay (3/8/2013)
That's why I used -3 milliseconds, Lynn.

Was actually talking to Sean and his code subtracting 1 millisecond.

Sean Lange SSC Guru Points: 286601 More actions · Answer 8

Lynn Pettis (3/8/2013)
Erin Ramsay (3/8/2013)
That's why I used -3 milliseconds, Lynn.
Was actually talking to Sean and his code subtracting 1 millisecond.

/facepalm

I think you both saw the error of my ways before I even posted them. Not sure if I fat fingered that or had a momentary lapse in brain activity. This is certainly something I am aware of.

Thanks to both of you for correcting me!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Update field in dimDate table

Cookies on SQLServerCentral