June 25, 2012 at 2:12 pm
Hi Guys,
I have to update all address fields in a table by a year. One of the columns has a datatype of Bigint, and the fields are all 13 characters that look like this '1276548319093'.
I have figured out how to get them into a format that looks like a date, and update it by a year. However, I can't figure out how to return them to Bigint so I can update the column. So far I have:
select CONVERT(nvarchar(50), DATEADD(year, 1, CONVERT(datetime,(select dateadd(second, message_date /1000 + 8*60*60, '19700101')), 121)), 121)
from messages
Or
select DATEADD(year, 1, CONVERT(datetime,(select dateadd(second, message_date /1000 + 8*60*60, '19700101')), 121))
from messages
Which seems to give me the exact same results so I think I will use this this one. When I try to then use this in a query to get the date difference and then work my way back to the Bigint like so:
update messages
set message_date = datediff (year, (select DATEADD(year, 1, CONVERT(datetime,(select dateadd(second, message_date /1000 + 8*60*60, '19700101')), 121))
from messages), '19700101 00:00:00.000') * 365*24*60*60 *(1000) -(8*60*60)
SQL gives me this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can anyone help me with where I am going wrong?
Thx
June 25, 2012 at 2:52 pm
I think it's the 'select's inside the functions. You don't need them.
Does this do it:
update messages
set message_date = cast((datediff (year, (select DATEADD(year, 1, CONVERT(datetime,(dateadd(second, message_date /1000 + 8*60*60, '19700101')), 121))),
'19700101 00:00:00.000')) as bigint) * 365*24*60*60 *(1000) -(8*60*60)
June 25, 2012 at 2:58 pm
Wow if this isn't enough of a reason to store datetime information as datetime instead of some oddball integer strangeness I don't know what is.
If this data was stored with the appropriate datatype this update is trivial.
/soapbox off
_______________________________________________________________
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/
June 25, 2012 at 3:06 pm
Thanks for taking the time to help David! That ran the update but the years were not correct. I will try to play around with it though.
June 25, 2012 at 4:23 pm
I have to echo Sean's comments. If you are storing date or date and time, you should really use the data types created for those sorts of values. Storing dates as something else guarantees that you have to do something special with them every time you use them. It can, and usually does, create havoc with the optimizer as it tries to figure out an effective plan, and makes the data difficult to work with in reporting tools like SSRS and Cognos.
Good luck!
June 25, 2012 at 4:28 pm
I hear you...it is not my database though. The software stores it this way and I have to update all date fields as a way of becoming accustomed to the software. I am not sure why certain tables used bigint or numeric istead of datetime and character dates, but in all cases where they did it appears to be a UNIX timestamp format.
June 25, 2012 at 9:11 pm
Hmm still can't get this one to work...any other suggestions?
June 25, 2012 at 9:27 pm
The following formula can certainly be simplified and the hard-coding can certainly be removed but I thought I'd leave that up to you. I wanted to make sure that the basic algorithm was clear before we go to optimizing it. For example, there's no need to add 0 years but wanted to show that the two conversions were identical except for adding a year to 1.
Basically, I used the 1970 epoch like you do to calculate the actual date (Don't care about time here. Adding the original number back in will preserve that) twice. Once as is and once with a year added. Then I take the number of days difference there to account for any time spans that may have 366 days in them due to a leap year, convert that to milliseconds, and add that back to the original value.
SELECT DATEDIFF(dd,
DATEADD(yy,0,CONVERT(DATETIME,'19700101') + 1276548319093/86400000),
DATEADD(yy,1,CONVERT(DATETIME,'19700101') + 1276548319093/86400000)
)
* CONVERT(BIGINT,86400000) + 1276548319093
86400000 is the number of milliseconds in a day. Since dates are based on whole and fractional days, all of the calculations above are based on the number of milliseconds in a day.
Of course, 1276548319093 is the original number you gave.
I've also not tested it for all dates/times but the math seems right.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2012 at 1:11 am
-- Convert time in milliseconds since 1970-01-01 to datetime
declare @MSTime bigint
set @MSTime = 1276548319093
select DT = dateadd(ms,@MSTime%86400000,(@MSTime/86400000)+25567)
-- Convert datetime to time in milliseconds since 1970-01-01
declare @datetime datetime
set @datetime = '2010-06-14 20:45:19.093'
selectMSTime =
(datediff(dd,25567,@DateTime)*00000086400000)+
datediff(ms,dateadd(dd,datediff(dd,0,@DateTime),0),@DateTime)
Results:
DT
-----------------------
2010-06-14 20:45:19.093
(1 row(s) affected)
MSTime
---------------------------------------
1276548319093
(1 row(s) affected)
June 26, 2012 at 10:23 am
Jeff, that worked perfectly and much simpler than the way I was approaching it. I was able to run the update on the entire table using that formula. Thank you!
Michael, thank you for your suggestion also. I am a SQL newb and this forum has been so helpful to me in my learning 😀
June 26, 2012 at 3:23 pm
nic79 (6/26/2012)
Jeff, that worked perfectly and much simpler than the way I was approaching it. I was able to run the update on the entire table using that formula. Thank you!Michael, thank you for your suggestion also. I am a SQL newb and this forum has been so helpful to me in my learning 😀
Just remember what I said. I didn't care about time down to the nearest millisecond except in the final result. Don't use my formulas for "perfect" conversions. Use Michael's instead. His are accurate to the millisecond .
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2012 at 7:59 pm
Jeff Moden (6/26/2012)
nic79 (6/26/2012)
Jeff, that worked perfectly and much simpler than the way I was approaching it. I was able to run the update on the entire table using that formula. Thank you!Michael, thank you for your suggestion also. I am a SQL newb and this forum has been so helpful to me in my learning 😀
Just remember what I said. I didn't care about time down to the nearest millisecond except in the final result. Don't use my formulas for "perfect" conversions. Use Michael's instead. His are accurate to the millisecond .
One thing to be aware of is that the conversion from milliseconds to datetime that I posted will be rounded off to the nearest 1/300 of a second, because that is the limit of accuracy for DATETIME.
For example, 1276548319094, will be rounded down to 2010-06-14 20:45:19.093, while 1276548319095 will be rounded up to 2010-06-14 20:45:19.097.
If you are using SQL 2008 or later, you can work around this using the DATETIME2 datatype.
-- Convert time in milliseconds since 1970-01-01 to datetime
declare @MSTime bigint
set @MSTime = 1276548319095
select DT = dateadd(ms,@MSTime%86400000,
convert(datetime2,convert(datetime,(@MSTime/86400000)+25567)))
-- Convert datetime to time in milliseconds since 1970-01-01
declare @datetime datetime2
set @datetime = '2010-06-14 20:45:19.095'
select @datetime
selectMSTime =
(datediff(dd,25567,@DateTime)*00000086400000)+
datediff(ms,dateadd(dd,datediff(dd,0,@DateTime),0),@DateTime)
June 26, 2012 at 10:14 pm
Thanks for the correction, Michael.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2012 at 2:05 pm
Ok I see what you mean, thanks guys! I am using 2008 so I could use datetime, but for my needs (working with an update on dummy databases) I don't think it will matter. Good to know for the future though 🙂
June 27, 2012 at 4:32 pm
nic79 (6/27/2012)
Ok I see what you mean, thanks guys! I am using 2008 so I could use datetime, but for my needs (working with an update on dummy databases) I don't think it will matter. Good to know for the future though 🙂
You posted in a SQL Server 2005 forum.
It's best to post in a forum for the version of SQL Server you are using, because people will usually supply answers for that version of SQL Server, and you may not get the most appropriate answer for your version of SQL Server.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply