Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 convert this long integer to a datetime i think?

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:

128605122619735209

Could 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]

Go to Top of Page

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]
Go to Top of Page

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]

Go to Top of Page

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
Go to Top of Page

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]
Go to Top of Page

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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 14:12:30
[code]
DECLARE @SourceValue BIGINT

SELECT @SourceValue = 128605122619735209

SELECT @SourceValue = (@SourceValue - 47966688000000000) / 600000000.0

SELECT DATEADD(MINUTE, @SourceValue, '17530101')[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-13 : 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
Go to Top of Page

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.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"
Go to Top of Page

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.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
Go to Top of Page

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.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 -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

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
) a

DT
------------------------
2008-07-14 12:31:00.000

(1 row(s) affected)




CODO ERGO SUM
Go to Top of Page

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
) 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 -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

jhermiz

3564 Posts

Posted - 2008-08-13 : 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 -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

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
) a

Results:
pwdLastSet DT
-------------------- ------------------------
128437745474218750 2008-01-02 19:09:00.000
0 NULL

(2 row(s) affected)






CODO ERGO SUM
Go to Top of Page

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
) 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 -- [url]http://weblogs.sqlteam.com/jhermiz[/url]
Go to Top of Page

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]
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 07:36:45
It is possible to get seconds out of the data
DECLARE	@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"
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -