Author |
Topic |
jhermiz
3564 Posts |
Posted - 2008-08-13 : 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:128605122619735209Could this be the number of seconds...err not sure.Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-13 : 11:56:28
|
this http://msdn.microsoft.com/en-us/library/ms679430(VS.85).aspx ? KH[spoiler]Time is always against us[/spoiler] |
|
|
jhermiz
3564 Posts |
Posted - 2008-08-13 : 12:05:59
|
quote: Originally posted by khtan this http://msdn.microsoft.com/en-us/library/ms679430(VS.85).aspx ? KH[spoiler]Time is always against us[/spoiler]
Thats the article but not sure how to do all the conversions...Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-08-13 : 12:16:37
|
no worries. The datetime expert will be here soon He just posted over at another datetime thread. KH[spoiler]Time is always against us[/spoiler] |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-13 : 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
3564 Posts |
Posted - 2008-08-13 : 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[spoiler]Time is always against us[/spoiler]
Thank God MVJ is still around...I sure as hell dont know how to do it :)Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-13 : 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
30421 Posts |
Posted - 2008-08-13 : 14:12:30
|
[code]DECLARE @SourceValue BIGINTSELECT @SourceValue = 128605122619735209SELECT @SourceValue = (@SourceValue - 47966688000000000) / 600000000.0SELECT DATEADD(MINUTE, @SourceValue, '17530101')[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-13 : 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
30421 Posts |
Posted - 2008-08-13 : 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" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-13 : 14:34:58
|
quote: Originally posted by Peso
DECLARE @SourceValue BIGINTSELECT @SourceValue = 128605122619735209SELECT @SourceValue = (@SourceValue - 47966688000000000) / 600000000.0SELECT 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/secwhich 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 minutesCODO ERGO SUM |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-13 : 14:35:03
|
According to this page http://www.timeanddate.com/date/duration.htmlthere are 55507 days between the two dates, not 55517.DECLARE @SourceValue BIGINTSELECT @SourceValue = 128605122619735209SELECT @SourceValue = (@SourceValue - 47958048000000000) / 600000000.0SELECT DATEADD(MINUTE, @SourceValue, '17530101') E 12°55'05.25"N 56°04'39.16" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-13 : 14:46:08
|
quote: Originally posted by Peso According to this page http://www.timeanddate.com/date/duration.htmlthere 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 |
|
|
jhermiz
3564 Posts |
Posted - 2008-08-13 : 15:15:32
|
quote: Originally posted by Peso According to this page http://www.timeanddate.com/date/duration.htmlthere are 55507 days between the two dates, not 55517.DECLARE @SourceValue BIGINTSELECT @SourceValue = 128605122619735209SELECT @SourceValue = (@SourceValue - 47958048000000000) / 600000000.0SELECT 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 1Adding a value to a 'datetime' column caused overflow.Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-13 : 15:26:24
|
Works OK for me:select DT = dateadd(minute,(convert(bigint,pwdLastSet)-47966688000000000)/600000000.0,'17530101')from ( -- Test Data select pwdLastSet = 128605122619735209 ) aDT------------------------2008-07-14 12:31:00.000(1 row(s) affected) CODO ERGO SUM |
|
|
jhermiz
3564 Posts |
Posted - 2008-08-13 : 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 ) aDT------------------------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 -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
jhermiz
3564 Posts |
Posted - 2008-08-13 : 15:45:57
|
Here are all the values for pwdLastSet:12799782263812500012860780643053794012861063285427284112799801516984375012800750726031319112800750734906319112800752897318048812800752905861847412860951352039917512806871893340565412826754598569250012810335992107273412810336035482551012816044411242256712861113093288735412818007872322060712817572436578125012817572940687500012817756195616040512817756196147293912853024462458500012857850398839800812817860586234375012849895927161500012850239190005750012818006526136757112856879020330875012862933353151426712862414878921943812855952296893750012855758645024136412857144098438250012861975737273898512861974963338302812855669490317000012862094397696343012862911073282219012862588772033496212858959508227842512859043442352848812862405062758530212862927231111680212861888427596113712855931324995375012862496438040416212857582959546875012862320999751660912860092091722214512858784240956888912856882217757390412858695942441031012861124754729019712857664138324037712856446324718500012861720426100616512855842600387462012835897498922250012863096551153987612818006526886752312858085724657776912862323231054753512855670563413375012856361633511750012856362683445250012857445058812567312856886181551500012861115500426345312860078262362606712855671146423000012862601940860360512862336027631408912855766460698000012861719229544854812861740275444408812862327142920167412861115044971482312858260918486326712861457825401947712861114859878916912861109639157093512858723989827148512818105675218750012855842494828340012855088141253500012856882516374000012857227866995625012859315557803268112856022616195402812862758662420713412858103719250904112861716241732596712862924764066996012856906851425602812862678295750187912863099446145668412861891589294923112841160729910500012858363122353351412862418871357540212857485114406250012855947691465436012861290294087156112861112903524324912861200255411162912855842807854500012861116793192499112856356820030715612862326005340861512863017631891110312855762631157634212862343227954691212863112276027711912856545377787250012861892920904626312862066281167575112862703757497269112862927639658068612857227095703375012859386942627733412862320016447377812856382418995250012862581676569883412863098297247242012855067099209250012857482792468814312862338570273786512859038589922042912862320169741309512861720304051042312856359891500793012861396906966447812856362864755225012859135262488046612856101756032500012856879879372750012860782402253126412861224499938218012855927311857500012862672318896082912861111485432068712861898832492772412861806899517472812861715349153634312856361378094875012862422709421221712856626782856625012856360745287375012862321435415157912856972899488704412856883324760625012825779338419125012861286799947865712859922481182773212858693134265878212862930243903357912857487358155000012856258165481331612861720229604389312856622771361375012861721977036167412856648926906875012862410059546623712862349288013151012856036388086875012862928421379242212855672662768375012862926256581790112863016409952480812856968137522622012857482120500068112861117716503322912862925845637272212851729747846902812856363740512944012861801735372834412856017075242000012856532721809131212861803874119595412855757243031000012857227043588000012856534659884625012857230699390500012862928802069761812862496351500589712855672151796375012862073146060431112862344919344814512856532700728375012858091419178305712861113855015821812855764109017875012857084781600625012856564590422000012856624505826342312861981759418798212856879003284000012862937559001553312861894443436197112862931305563156612818006531855470512818006531855470512862066256079380512818007742869764212818007742916638912818007742947888712818007742994763412818007743026013212818007743072887912818007743119762612818007743151012412818007743197887112818007693651329212835801371319625012818007743307261412818007743354136112818007743385385912818007743432260612818007743463510412818007743510385112818007743541634912818007743572884712818007743619759412818007743651009212818007743729133712818007743776008412818007743807258212821910192659625012818007743885382712818007743932257412818007743963507212818007744010381912818007744041631712818007744088506412818007744119756212818007744166630912818007744197880712818007744244755412818007744276005212832776787286125012818007741979144912818007742010394712818007742057269412818007742088519212818007742119769012818007742166643712818007742213518412818007742260393112856996614960818412818007742338517612818007742369767412818007742416642112818007742447891912818007742494766612818007742526016412818007742572891112818007742604140912818007742682265412818007742729140112827253842499506812818006527511748312818006527511748312861194426353790512859301274059152312860683046059518112829146177046888012818006527574247912818006527589872812860594369703999112818006527605497712818006527605497712818006527621122612851785447176375012818006527636747512862591980200534612841268551035500012847486195158549512861978905819822812818006527683622212856537373438779212859402161168246912856358227832606812833573766794500012861800213109970612861199664253595212818006527761746712818006527777371612818006527777371612818006527792996512858178423020906512818006527808621412827448859183625012818006527824246312833930087572250012818006527839871212862577271035229012855754048272750012818006527886745912818006527886745912818006527902370812818006527902370812818006527917995712846099419409250012852756229461875012840903282441750012818006527933620612818006527949245512818006527949245512818006527964870412818006527980495312818006527996120212818006528011745112818006528011745112818006528027370012818006528027370012818006528042994912818006528042994912818006528058619812818006528058619812858723913595724412818006528074244712818006528089869612818006528089869612857766800408253212818006528105494512818006528121119412831481535259875012818007872306435812818019718718750012818115391750000012818007872322060712818007872322060700128180078723220607012818201120343750012818007872337685612818007872353310512818038055187500012818007872353310512818007872368935412818006535230448912818007636901692412818007636901692412818007636917317312818007636917317312818007636917317312818007636917317312818007636932942212818007636932942212818007636932942212818007636948567112818007636948567112818007636948567112818007636948567112818007636964192012818007636964192012818007636964192012818007636964192012818007636979816912818007636979816912818007636979816912818007636995441812818007636995441812818007636995441812818007636995441812818007637011066712818007637011066712818007637011066712818007637011066712818007637026691612818007637026691612818034919294794412818036765139840712855693999941407012857318968507821812863099072802468112818547810182840312818547810635968212862666975434490012863104712066768812827537622336750012862076615535874812862098553238186412856884543673331212862931839652045112862336609093725312862076685472479612862076730815649212856365896189500012862929487399781412856624613998625012862076771908873212862347314153740612861907735341913612861376702576212712862336658843444012818622809403469412862411045236200512859301287365027512861807241198562712826759830311867112862930280475966112862441881556135112862077026561863612862076974468780412862326802825284612862077103014010012862939517432571412856894522792000012855672757440250012863105565231229512862077144154108412829673537331173112856365149841125012862931980462742712855961542369875012862077192934734012857756110651871412861111646639462612862077224621828412860338353957332112862422556484700512862410847143718312862098490206936412855934058508068712862177646095069312862930478181609112856021189839625012855966217053250012818703198421875012818703198468750012818703198500000012818703198546875012842546219045129212818703198609375012818703198640625012842546881646167312842545234347542112818703198765625012818703198796875012818703198828125012818703198875000012818703198906250012818703198937500012818703198984375012818703199015625012818703199046875012818703199078125012819041015348875012819047292666237212855446987973625012819047385060067812818979282828885512862585093787677912856884278270478412818793507843750012818895694765625012819057260974125012819058085170250012819058218992625012819058257289500012819058297289500012819058342648875012819058419242625012819058467305125012819058522742625012819058569695750012819058659789500012819058707555125012819058763688375012819059456230375012819061173808500012819061228183500012819061906248625012819061976858000012819405877868875012855329415900375012857485756703194512857661018422671312819671364982949112820017368345306512863096664590035612855250655302000012859384164179727812861718606269367912823642344627750012824222512935375012824222601404125012824222750013500012824222786263500012824246734093375012824419254937549212824419260937357212825616166312500012825616197218750012825616316226250012826147689225375012826650085023875012858101098743872112827253525704528612827275138004980212827275362566043012827344961777125012828134574669625012828477494922095312828479561084500012859394029033371712829760340670875012830443324953775812861723647954093912860619631993688312832364609397875012833385177425230412833385246694065212833385298606635812833385347207300012856443327251578912857064973576875012857006032592625012834187029352000012861982006502374412834608344737588512834689885263411412834689988892760312834690049823630312834690121345910212834690941996750012856987002242750012860599322190363812860000210303013112837276735394875012860709238286184912859900853807336512838162549613625012860505154687340112838497190660500012838498042988625012854138358196827212839007248238625012839613765504250012839614323285500012839614868082375012839615021141056012839615231973000012840644527941750012840731247160500012841526149038825001284601751020819081284143307847300001284202883776454451284254176973587501284256817846212961285872671670659880128437745474218750128437745802812500128630218270344770128582613244769696128602008513147255128623257097367944128613962500373566128611140613587801128449640362626714128450492812610566128582656934502971128450493752135803128450607406190491128454191957125977128454192558215880128612143805549033128454942061018020128457673768031294128554329730711250128460864954027500128461754761431250128620685079028119128467849600657500128468918703643702128629306557203596128472367436767500128472368360048750128480121467454230128480121920888828128484468323385000128565316712520000128630218102230679128575841663881120128498902400254787128498902057288232128583596575368343128528242801493750128528242401650000128508583255915494128508582714200211128509430028618750128516959875309860128592999935058730128624230477162774128520530086342500128520537580699953128529128761016492128529129455347418128529129874827326128529130207008112128533454782578750128535182547944653128616408005194073128538754509359614128541270015938952128544897348149315128544896775340481128544897932364326128544898436423600012855161521662750012855434406776875012855424731846000012863021851050670312856468896197000012856547396168500012857062527724875012856970849797375012857486525630125012856975051201519612857227158656500012857149227599108412863040034609233512857681868697958012857688575600107012858119371098279312858188486139361712858265649357111512858265680747334712858783465723027512858361684294083412858361876875308712858705810659902812859328937229484812858976992137834001285939358842998431285990821180403071285991227482350271285948667127263301286051226197352091285992065027999191286051205663154301286054046746163481286052264877461531286137495325170551286180868583033561286258370209404701286241536970711121286252593223970511286269588399782701286293132101611310One of these is causing this thing to overflow?Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-08-13 : 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 ) aResults:pwdLastSet DT-------------------- ------------------------128437745474218750 2008-01-02 19:09:00.0000 NULL(2 row(s) affected) CODO ERGO SUM |
|
|
jhermiz
3564 Posts |
Posted - 2008-08-14 : 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 ) aResults:pwdLastSet DT-------------------- ------------------------128437745474218750 2008-01-02 19:09:00.0000 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 1The conversion of the nvarchar value '127997822638125000' overflowed an int column. Maximum integer value exceeded.Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
jhermiz
3564 Posts |
Posted - 2008-08-14 : 07:24:05
|
Got it with this:dateadd(minute,(convert(bigint,nullif(convert(bigint, pwdLastSet),0))-47966688000000000)/600000000.0,'17530101')Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-14 : 07:36:45
|
It is possible to get seconds out of the dataDECLARE @Year2000 BIGINTSET @Year2000 = 125902944000000000 -- 100 nanoseconds between 16000101 and 20000101--SET @Year2000 = 125911584000000000 -- Without the 10 days deductionSELECT *, DATEADD(SECOND, (NULLIF(t, 0) - @Year2000) / 10000000.0, '20000101')FROM @Sample E 12°55'05.25"N 56°04'39.16" |
|
|
Next Page
|