Author |
Topic  |
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 08/13/2008 : 11:53:12
|
Ive got an ldap query I use in SQL 2005 to pull user information. One of the fields I pull in is called pwdLastSet. Apparently this has some sort of long or integer type value like: 128594866712726330. Some people have the value 0 which I found out means the password is expired...but I need to find out when there is say 5 or 7 days left before the pwd is expired. How do I convert numbers like this:
128605122619735209
Could this be the number of seconds...err not sure.
Weblog -- http://weblogs.sqlteam.com/jhermiz |
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
|
khtan
In (Som, Ni, Yak)
Singapore
17689 Posts |
Posted - 08/13/2008 : 12:16:37
|
no worries. The datetime expert will be here soon 
He just posted over at another datetime thread.
KH Time is always against us
|
Edited by - khtan on 08/13/2008 12:17:41 |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/13/2008 : 12:34:57
|
"number of 100 nanosecond intervals since January 1, 1601 (UTC)."
I think this is easy to do in SQL 2008 with the new extended datetime datatypes. Little harder in 2005 and before, since the earliest datetime value is 1753-01-01.
Anyone know the number of 100 ns intervals between 1601-01-01 and 1753-01-01? Or even the number of days?
CODO ERGO SUM |
 |
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 08/13/2008 : 13:10:48
|
quote: Originally posted by khtan
no worries. The datetime expert will be here soon 
He just posted over at another datetime thread.
KH Time is always against us
Thank God MVJ is still around...I sure as hell dont know how to do it :)
Weblog -- http://weblogs.sqlteam.com/jhermiz |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/13/2008 : 14:07:27
|
There are 55517 days between 16010101 and 17530101.
E 12°55'05.25" N 56°04'39.16" |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/13/2008 : 14:12:30
|
DECLARE @SourceValue BIGINT
SELECT @SourceValue = 128605122619735209
SELECT @SourceValue = (@SourceValue - 47966688000000000) / 600000000.0
SELECT DATEADD(MINUTE, @SourceValue, '17530101')
E 12°55'05.25" N 56°04'39.16" |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/13/2008 : 14:25:31
|
quote: Originally posted by Peso
There are 55517 days between 16010101 and 17530101.
E 12°55'05.25" N 56°04'39.16"
Did you use SQL 2008 to find that?
CODO ERGO SUM |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/13/2008 : 14:31:03
|
No. I used my abacus 
Not really. I just added 200 years to both the dates and took that difference (18010101-19530101) instead.
E 12°55'05.25" N 56°04'39.16" |
Edited by - SwePeso on 08/13/2008 14:31:17 |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/13/2008 : 14:34:58
|
quote: Originally posted by Peso
DECLARE @SourceValue BIGINT
SELECT @SourceValue = 128605122619735209
SELECT @SourceValue = (@SourceValue - 47966688000000000) / 600000000.0
SELECT DATEADD(MINUTE, @SourceValue, '17530101')
E 12°55'05.25" N 56°04'39.16"
Good work.
For those of you wondering, 47966688000000000 = 55517 days * 86400 Sec/Day * 10000000 100ms periods/sec which is the offset from 16010101 to 17530101 in 100 ms periods in order to be able to use DATEADD to convert to a date.
Dividing 100ms periods by 600000000 converts it to minutes
CODO ERGO SUM |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/13/2008 : 14:35:03
|
According to this page http://www.timeanddate.com/date/duration.html there are 55507 days between the two dates, not 55517.
DECLARE @SourceValue BIGINT
SELECT @SourceValue = 128605122619735209
SELECT @SourceValue = (@SourceValue - 47958048000000000) / 600000000.0
SELECT DATEADD(MINUTE, @SourceValue, '17530101')
E 12°55'05.25" N 56°04'39.16" |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/13/2008 : 14:46:08
|
quote: Originally posted by Peso
According to this page http://www.timeanddate.com/date/duration.html there are 55507 days between the two dates, not 55517.
That is taking into account the switch from the Julian calendar to Gregorian calendar that happened in 1752 in most English speaking countries. I think testing would be needed to determine if Windows is taking that into account.
I believe the new SQL 2008 datetime datatype does not. The switch from Julian calendar to Gregorian calendar happened at different times in different countries, so it is hard to say what is actually correct.
Also, the time is in UTC, so you may need to convert to local time if you need it that way.
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 08/13/2008 14:50:07 |
 |
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 08/13/2008 : 15:15:32
|
quote: Originally posted by Peso
According to this page http://www.timeanddate.com/date/duration.html there are 55507 days between the two dates, not 55517.
DECLARE @SourceValue BIGINT
SELECT @SourceValue = 128605122619735209
SELECT @SourceValue = (@SourceValue - 47958048000000000) / 600000000.0
SELECT DATEADD(MINUTE, @SourceValue, '17530101')
E 12°55'05.25" N 56°04'39.16"
Almost there ..I tried the following:
SELECT DATEADD(MINUTE, (CONVERT(BIGINT, pwdLastSet) - 47966688000000000) / 600000000.0, '17530101'),
The query is ready to display the results and flashes and comes up with:
Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow.
Weblog -- http://weblogs.sqlteam.com/jhermiz |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/13/2008 : 15:26:24
|
Works OK for me:
select
DT =
dateadd(minute,(convert(bigint,pwdLastSet)-47966688000000000)/600000000.0,'17530101')
from
(
-- Test Data
select pwdLastSet = 128605122619735209
) a
DT
------------------------
2008-07-14 12:31:00.000
(1 row(s) affected)
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 08/13/2008 15:33:31 |
 |
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 08/13/2008 : 15:42:14
|
quote: Originally posted by Michael Valentine Jones
Works OK for me:
select
DT =
dateadd(minute,(convert(bigint,pwdLastSet)-47966688000000000)/600000000.0,'17530101')
from
(
-- Test Data
select pwdLastSet = 128605122619735209
) a
DT
------------------------
2008-07-14 12:31:00.000
(1 row(s) affected)
CODO ERGO SUM
Ya im not sure why its not working here could be some of the sample data causing it...is there any way you can think of to eliminate this error based on some sort of exception or erroneous data?
Weblog -- http://weblogs.sqlteam.com/jhermiz |
 |
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 08/13/2008 : 15:45:57
|
Here are all the values for pwdLastSet:
127997822638125000 128607806430537940 128610632854272841 127998015169843750 128007507260313191 128007507349063191 128007528973180488 128007529058618474 128609513520399175 128068718933405654 128267545985692500 128103359921072734 128103360354825510 128160444112422567 128611130932887354 128180078723220607 128175724365781250 128175729406875000 128177561956160405 128177561961472939 128530244624585000 128578503988398008 128178605862343750 128498959271615000 128502391900057500 128180065261367571 128568790203308750 128629333531514267 128624148789219438 128559522968937500 128557586450241364 128571440984382500 128619757372738985 128619749633383028 128556694903170000 128620943976963430 128629110732822190 128625887720334962 128589595082278425 128590434423528488 128624050627585302 128629272311116802 128618884275961137 128559313249953750 128624964380404162 128575829595468750 128623209997516609 128600920917222145 128587842409568889 128568822177573904 128586959424410310 128611247547290197 128576641383240377 128564463247185000 128617204261006165 128558426003874620 128358974989222500 128630965511539876 128180065268867523 128580857246577769 128623232310547535 128556705634133750 128563616335117500 128563626834452500 128574450588125673 128568861815515000 128611155004263453 128600782623626067 128556711464230000 128626019408603605 128623360276314089 128557664606980000 128617192295448548 128617402754444088 128623271429201674 128611150449714823 128582609184863267 128614578254019477 128611148598789169 128611096391570935 128587239898271485 128181056752187500 128558424948283400 128550881412535000 128568825163740000 128572278669956250 128593155578032681 128560226161954028 128627586624207134 128581037192509041 128617162417325967 128629247640669960 128569068514256028 128626782957501879 128630994461456684 128618915892949231 128411607299105000 128583631223533514 128624188713575402 128574851144062500 128559476914654360 128612902940871561 128611129035243249 128612002554111629 128558428078545000 128611167931924991 128563568200307156 128623260053408615 128630176318911103 128557626311576342 128623432279546912 128631122760277119 128565453777872500 128618929209046263 128620662811675751 128627037574972691 128629276396580686 128572270957033750 128593869426277334 128623200164473778 128563824189952500 128625816765698834 128630982972472420 128550670992092500 128574827924688143 128623385702737865 128590385899220429 128623201697413095 128617203040510423 128563598915007930 128613969069664478 128563628647552250 128591352624880466 128561017560325000 128568798793727500 128607824022531264 128612244999382180 128559273118575000 128626723188960829 128611114854320687 128618988324927724 128618068995174728 128617153491536343 128563613780948750 128624227094212217 128566267828566250 128563607452873750 128623214354151579 128569728994887044 128568833247606250 128257793384191250 128612867999478657 128599224811827732 128586931342658782 128629302439033579 128574873581550000 128562581654813316 128617202296043893 128566227713613750 128617219770361674 128566489269068750 128624100595466237 128623492880131510 128560363880868750 128629284213792422 128556726627683750 128629262565817901 128630164099524808 128569681375226220 128574821205000681 128611177165033229 128629258456372722 128517297478469028 128563637405129440 128618017353728344 128560170752420000 128565327218091312 128618038741195954 128557572430310000 128572270435880000 128565346598846250 128572306993905000 128629288020697618 128624963515005897 128556721517963750 128620731460604311 128623449193448145 128565327007283750 128580914191783057 128611138550158218 128557641090178750 128570847816006250 128565645904220000 128566245058263423 128619817594187982 128568790032840000 128629375590015533 128618944434361971 128629313055631566 128180065318554705 128180065318554705 128620662560793805 128180077428697642 128180077429166389 128180077429478887 128180077429947634 128180077430260132 128180077430728879 128180077431197626 128180077431510124 128180077431978871 128180076936513292 128358013713196250 128180077433072614 128180077433541361 128180077433853859 128180077434322606 128180077434635104 128180077435103851 128180077435416349 128180077435728847 128180077436197594 128180077436510092 128180077437291337 128180077437760084 128180077438072582 128219101926596250 128180077438853827 128180077439322574 128180077439635072 128180077440103819 128180077440416317 128180077440885064 128180077441197562 128180077441666309 128180077441978807 128180077442447554 128180077442760052 128327767872861250 128180077419791449 128180077420103947 128180077420572694 128180077420885192 128180077421197690 128180077421666437 128180077422135184 128180077422603931 128569966149608184 128180077423385176 128180077423697674 128180077424166421 128180077424478919 128180077424947666 128180077425260164 128180077425728911 128180077426041409 128180077426822654 128180077427291401 128272538424995068 128180065275117483 128180065275117483 128611944263537905 128593012740591523 128606830460595181 128291461770468880 128180065275742479 128180065275898728 128605943697039991 128180065276054977 128180065276054977 128180065276211226 128517854471763750 128180065276367475 128625919802005346 128412685510355000 128474861951585495 128619789058198228 128180065276836222 128565373734387792 128594021611682469 128563582278326068 128335737667945000 128618002131099706 128611996642535952 128180065277617467 128180065277773716 128180065277773716 128180065277929965 128581784230209065 128180065278086214 128274488591836250 128180065278242463 128339300875722500 128180065278398712 128625772710352290 128557540482727500 128180065278867459 128180065278867459 128180065279023708 128180065279023708 128180065279179957 128460994194092500 128527562294618750 128409032824417500 128180065279336206 128180065279492455 128180065279492455 128180065279648704 128180065279804953 128180065279961202 128180065280117451 128180065280117451 128180065280273700 128180065280273700 128180065280429949 128180065280429949 128180065280586198 128180065280586198 128587239135957244 128180065280742447 128180065280898696 128180065280898696 128577668004082532 128180065281054945 128180065281211194 128314815352598750 128180078723064358 128180197187187500 128181153917500000 128180078723220607 128180078723220607 0 0 128180078723220607 0 128182011203437500 128180078723376856 128180078723533105 128180380551875000 128180078723533105 128180078723689354 128180065352304489 128180076369016924 128180076369016924 128180076369173173 128180076369173173 128180076369173173 128180076369173173 128180076369329422 128180076369329422 128180076369329422 128180076369485671 128180076369485671 128180076369485671 128180076369485671 128180076369641920 128180076369641920 128180076369641920 128180076369641920 128180076369798169 128180076369798169 128180076369798169 128180076369954418 128180076369954418 128180076369954418 128180076369954418 128180076370110667 128180076370110667 128180076370110667 128180076370110667 128180076370266916 128180076370266916 128180349192947944 128180367651398407 128556939999414070 128573189685078218 128630990728024681 128185478101828403 128185478106359682 128626669754344900 128631047120667688 128275376223367500 128620766155358748 128620985532381864 128568845436733312 128629318396520451 128623366090937253 128620766854724796 128620767308156492 128563658961895000 128629294873997814 128566246139986250 128620767719088732 128623473141537406 128619077353419136 128613767025762127 128623366588434440 128186228094034694 128624110452362005 128593012873650275 128618072411985627 128267598303118671 128629302804759661 128624418815561351 128620770265618636 128620769744687804 128623268028252846 128620771030140100 128629395174325714 128568945227920000 128556727574402500 128631055652312295 128620771441541084 128296735373311731 128563651498411250 128629319804627427 128559615423698750 128620771929347340 128577561106518714 128611116466394626 128620772246218284 128603383539573321 128624225564847005 128624108471437183 128620984902069364 128559340585080687 128621776460950693 128629304781816091 128560211898396250 128559662170532500 128187031984218750 128187031984687500 128187031985000000 128187031985468750 128425462190451292 128187031986093750 128187031986406250 128425468816461673 128425452343475421 128187031987656250 128187031987968750 128187031988281250 128187031988750000 128187031989062500 128187031989375000 128187031989843750 128187031990156250 128187031990468750 128187031990781250 128190410153488750 128190472926662372 128554469879736250 128190473850600678 128189792828288855 128625850937876779 128568842782704784 128187935078437500 128188956947656250 128190572609741250 128190580851702500 128190582189926250 128190582572895000 128190582972895000 128190583426488750 128190584192426250 128190584673051250 128190585227426250 128190585696957500 128190586597895000 128190587075551250 128190587636883750 128190594562303750 128190611738085000 128190612281835000 128190619062486250 128190619768580000 128194058778688750 128553294159003750 128574857567031945 128576610184226713 128196713649829491 128200173683453065 128630966645900356 128552506553020000 128593841641797278 128617186062693679 128236423446277500 128242225129353750 128242226014041250 128242227500135000 128242227862635000 128242467340933750 128244192549375492 128244192609373572 128256161663125000 128256161972187500 128256163162262500 128261476892253750 128266500850238750 128581010987438721 128272535257045286 128272751380049802 128272753625660430 128273449617771250 128281345746696250 128284774949220953 128284795610845000 128593940290333717 128297603406708750 128304433249537758 128617236479540939 128606196319936883 128323646093978750 128333851774252304 128333852466940652 128333852986066358 128333853472073000 128564433272515789 128570649735768750 128570060325926250 128341870293520000 128619820065023744 128346083447375885 128346898852634114 128346899888927603 128346900498236303 128346901213459102 128346909419967500 128569870022427500 128605993221903638 128600002103030131 128372767353948750 128607092382861849 128599008538073365 128381625496136250 128605051546873401 128384971906605000 128384980429886250 128541383581968272 128390072482386250 128396137655042500 128396143232855000 128396148680823750 128396150211410560 128396152319730000 128406445279417500 128407312471605000 128415261490388250 0 128460175102081908 128414330784730000 128420288377645445 128425417697358750 128425681784621296 128587267167065988 0 128437745474218750 128437745802812500 128630218270344770 128582613244769696 128602008513147255 128623257097367944 128613962500373566 128611140613587801 128449640362626714 128450492812610566 128582656934502971 128450493752135803 128450607406190491 128454191957125977 128454192558215880 128612143805549033 128454942061018020 128457673768031294 128554329730711250 128460864954027500 128461754761431250 128620685079028119 128467849600657500 128468918703643702 128629306557203596 128472367436767500 128472368360048750 128480121467454230 128480121920888828 128484468323385000 128565316712520000 128630218102230679 128575841663881120 128498902400254787 128498902057288232 128583596575368343 128528242801493750 128528242401650000 128508583255915494 128508582714200211 128509430028618750 128516959875309860 128592999935058730 128624230477162774 128520530086342500 128520537580699953 128529128761016492 128529129455347418 128529129874827326 128529130207008112 128533454782578750 128535182547944653 128616408005194073 128538754509359614 128541270015938952 128544897348149315 128544896775340481 128544897932364326 128544898436423600 0 128551615216627500 128554344067768750 128554247318460000 128630218510506703 128564688961970000 128565473961685000 128570625277248750 128569708497973750 128574865256301250 128569750512015196 128572271586565000 128571492275991084 128630400346092335 128576818686979580 128576885756001070 128581193710982793 128581884861393617 128582656493571115 128582656807473347 128587834657230275 128583616842940834 128583618768753087 128587058106599028 128593289372294848 128589769921378340 0 128593935884299843 128599082118040307 128599122748235027 128594866712726330 128605122619735209 128599206502799919 128605120566315430 128605404674616348 128605226487746153 128613749532517055 128618086858303356 128625837020940470 128624153697071112 128625259322397051 128626958839978270 128629313210161131 0
One of these is causing this thing to overflow?
Weblog -- http://weblogs.sqlteam.com/jhermiz |
 |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 08/13/2008 : 17:24:47
|
I think the overflows come from the values of 0.
The code below should eliminate the error.
select
pwdLastSet,
DT =
dateadd(minute,(convert(bigint,nullif(pwdLastSet,0))-47966688000000000)/600000000.0,'17530101')
from
(
-- Test Data
select pwdLastSet = 128437745474218750 union all
select pwdLastSet = 0
) a
Results:
pwdLastSet DT
-------------------- ------------------------
128437745474218750 2008-01-02 19:09:00.000
0 NULL
(2 row(s) affected)
CODO ERGO SUM |
 |
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 08/14/2008 : 07:14:52
|
quote: Originally posted by Michael Valentine Jones
I think the overflows come from the values of 0.
The code below should eliminate the error.
select
pwdLastSet,
DT =
dateadd(minute,(convert(bigint,nullif(pwdLastSet,0))-47966688000000000)/600000000.0,'17530101')
from
(
-- Test Data
select pwdLastSet = 128437745474218750 union all
select pwdLastSet = 0
) a
Results:
pwdLastSet DT
-------------------- ------------------------
128437745474218750 2008-01-02 19:09:00.000
0 NULL
(2 row(s) affected)
CODO ERGO SUM
Im getting closer with this:
dateadd(minute,(convert(bigint,nullif(pwdLastSet,0))-47966688000000000)/600000000.0,'17530101')
The latest error message is:
Msg 248, Level 16, State 1, Line 1 The conversion of the nvarchar value '127997822638125000' overflowed an int column. Maximum integer value exceeded.
Weblog -- http://weblogs.sqlteam.com/jhermiz |
 |
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 08/14/2008 : 07:24:05
|
Got it with this:
dateadd(minute,(convert(bigint,nullif(convert(bigint, pwdLastSet),0))-47966688000000000)/600000000.0,'17530101')
Weblog -- http://weblogs.sqlteam.com/jhermiz |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/14/2008 : 07:36:45
|
It is possible to get seconds out of the dataDECLARE @Year2000 BIGINT
SET @Year2000 = 125902944000000000 -- 100 nanoseconds between 16000101 and 20000101
--SET @Year2000 = 125911584000000000 -- Without the 10 days deduction
SELECT *,
DATEADD(SECOND, (NULLIF(t, 0) - @Year2000) / 10000000.0, '20000101')
FROM @Sample
E 12°55'05.25" N 56°04'39.16" |
Edited by - SwePeso on 08/14/2008 07:41:13 |
 |
|
Topic  |
|