| Author |
Topic |
|
dougancil
Posting Yak Master
217 Posts |
 Posted - 2011-03-23 : 12:33:05
|
| I have a table that has a column that I am trying to convert to a readable date time value. I am using the following equation but and getting a date but not a time value: dateadd(d,moncalladd.adddate, '12/31/1899')-(moncalladd.adddate/1000)/60/1440+1and here is my result:2011-03-23 00:00:00.000can anyone suggest how I can get both the date and the time in my formula?Thank youDoug |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-03-23 : 13:02:53
|
| Can you explain what you are trying to achomplish? What datatype is AddDate?As a side note, it would be better if you used an ANSI or ISO (my prefference) standard for date strings. |
 |
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-23 : 13:48:40
|
| adddate is an integer. The database that I'm pulling this information from, shows a date and time that a document was added to a website. I'm trying to run a query to show that in a user friendly manner so that I can post that information to a web site. I don't have any control over how that data is put into the database, only how it's retrieved and displayed. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-03-23 : 14:12:51
|
| And that integer represents what?Is it a day offset from 1899-12-31? Or some other increment from an epoc of some sort? I guess I don't see how you have Time information stored. |
|
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-23 : 14:20:56
|
| one of the values of addDate in the table is 38490 and according to the vendor"It is the number of days from December 31st, 1899."but it's stored as an integer value. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-03-23 : 14:43:09
|
"It is the number of days from December 31st, 1899."this ^ implies that there is no time info stored in the column. Best you will get is Day Info.Corey snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"  |
|
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-23 : 14:59:02
|
| Seven,That's not true because elsewhere in my query, I'm pulling the datetime from another field in the same database that is an integer as well, but that same formula isn't working here. Here's my other formula and the result of it:dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440 +1and the resulting data is 2011-03-24 11:27:00.000 I have tried with n replacing the d in my query that does not produce this result. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-03-23 : 15:24:32
|
| I'm confused. Is "moncalladd.adddate" is DAYS or MINUTES? It appears that "moncalladd.Timestamp" is in Minutes as N is the datepart abbreviation for Minute. If "moncalladd.adddate" is in minutes then just repalce Timestamp with adddate in the formula that works with minutes. |
|
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-23 : 15:35:37
|
| Lamprey,moncalladd.adddate is an integer and according to the vendor both are in minutes. I've tried the minutes formula on the moncalladd.adddate and it will show me the time but not the date. I've also tried the datepart abbreviation for hours and seconds just to see if any of those would produce the results that I need, which they did not."hours" result: 1904-08-20 13:00:00.000"seconds" result: 1900-01-01 11:17:01.000 |
|
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-23 : 16:18:45
|
| What could I use to just return the date? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-03-23 : 16:21:59
|
I don't see what the problem is. Given the data you have suppied I determined the difference between the dates in minutes and used that value in the formula to get a date (that I think is correct). SELECT DATEDIFF(n, '12/31/1899', '2011-03-24 11:27:00.000')--58500687DECLARE @MyDate INT = 58500687SELECT dateadd(n,@MyDate, '12/31/1899')-(@MyDate/1000)/60/1440 +1 Can you supply some sample data for the TimeStamp and AddDate columns for testing? |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-03-23 : 16:46:04
|
First...quote:
Originally posted by dougancil one of the values of addDate in the table is 38490 and according to the vendor"It is the number of days from December 31st, 1899."but it's stored as an integer value.
Then...quote:
Originally posted by dougancil
Lamprey,moncalladd.adddate is an integer and according to the vendor both are in minutes. I've tried the minutes formula on the moncalladd.adddate and it will show me the time but not the date. I've also tried the datepart abbreviation for hours and seconds just to see if any of those would produce the results that I need, which they did not."hours" result: 1904-08-20 13:00:00.000"seconds" result: 1900-01-01 11:17:01.000
So, my first answer was correct given your description of the data...I think Lamprey has you though.CoreysnSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!" |
|
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-24 : 11:10:12
|
Lamprey,here is some sample data:TimeStamp AddDate--------------------------------55426893 3849055427287 3849155427289 38491 Oh and here is the complete query that I'm working with:select moncalladd.opname, moncalladd.schedname, moncalladd.firstlisting, dateadd(d,moncalladd.adddate, '12/31/1899')-(moncalladd.adddate/1000)/60/1440+1 as adddate, moncalladd.initials, dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440 +1 as oncalldate from mdr.dbo.moncalladd inner join mdr.dbo.moncalldeleteon moncalladd.schedname = moncalldelete.schedname and moncalladd.timestamp= moncalldelete.timestampWHERE dateadd(d,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440+1 >= '03-21-2011' order by oncalldate |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-03-24 : 11:42:00
|
Well it sounds like there is a miss communication between you and the vendor (or they are liying).Two things:1. The Timestamp appears to be in minutes (assumably from some epoch, my guess is 1900-01-01). AddDate appesars to be in Days, also from the same epoch.2. The data calculation is far mor complicated than it needs to be.Example:DECLARE @T TABLE ([TimeStamp] INT, AddDate INT)INSERT @T ([TimeStamp], AddDate)VALUES(55426893, 38490),(55427287, 38491),(55427289, 38491)SELECT DATEADD(DAY, AddDate, '19000101'), DATEADD(MINUTE, [TimeStamp], '12/31/1899')-([TimeStamp]/1000)/60/1440+1, DATEADD(MINUTE, [TimeStamp], '19000101')FROM @T So given your sample data, AddDate does NOT contain time information. Unless it from a different epoch. |
|
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-28 : 11:45:09
|
Lamprey,Ok so then I've found that there is another field in the database called AddTime, here is a sample of the data from that field:StartOnCallDate StartOncallTime AddDate AddTime--------------- --------------- ------- -------38490 1020 38490 129338494 1020 38491 247 So then I think that is the field I'm missing. How would I calculate time with the formula that I have and that additional field? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-03-28 : 12:19:43
|
Soemthing like:DECLARE @T TABLE (AddDate INT, AddTime INT)INSERT @T (AddDate, AddTime)VALUES(38490, 1293),(38491, 247)SELECT DATEADD(MINUTE, AddTime, DATEADD(DAY, AddDate, '19000101'))FROM @T |
|
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-29 : 14:40:49
|
One last question in regards to this query:select moncalladd.opname, moncalladd.schedname, moncalladd.firstlisting, DATEADD(MINUTE, moncalladd.AddTime, DATEADD(DAY, moncalladd.AddDate, '12/31/1899')) as added,moncalladd.initials, dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440 +1 as oncalldatefrom mdr.dbo.moncalladd inner join mdr.dbo.moncalldelete on moncalladd.schedname = moncalldelete.schedname and moncalladd.timestamp= moncalldelete.timestampWHERE dateadd(n,moncalladd.Timestamp, '12/31/1899')-(moncalladd.timestamp/1000)/60/1440+1 >= '03-21-2011' and moncalladd.schedname='CAP.NEURO'order by oncalldate If I run that query as is .. it runs fine but when I add the following line:dateadd(n, moncalladd.startoncalldate, +1)+(moncall.startoncalltime/1440)+(moncalladd.Duration/1440) as addeddate, anywhere in my query I get this error:The column prefix 'moncall' does not match with a table name or alias name used in the query.What am I not seeing here? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-03-29 : 17:57:15
|
quote:
Originally posted by dougancil...anywhere in my query I get this error:The column prefix 'moncall' does not match with a table name or alias name used in the query.What am I not seeing here?
Based on the code you posted, it's because you don't have a table name or alias name of 'moncall' in your query. Just like the error message says.from mdr.dbo.moncalladd inner join mdr.dbo.moncalldelete CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-03-29 : 18:21:28
|
Welcome back Corey!
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-03-30 : 05:37:51
|
| Thanks Peso!CoreysnSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!" |
|
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-03-30 : 11:45:54
|
| Michael,That doesnt make sense though ... if I remove this line:dateadd(n, moncalladd.startoncalldate, +1)+(moncall.startoncalltime/1440)+(moncalladd.Duration/1440) as addeddate,then my query works fine ... |
|
|
|
Next Page
|
Trying to get date and time from value