Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, youâ€™ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
DECLARE @D2 INT
SET @D2= -27444
DECLARE @intVal INT
DECLARE @date DATETIME
--FLOOR(FLOOR(IF(D2<0,D2+65536,D2)/1000+70,1)*365.25+0.75,1)+MOD(IF(D2<0,D2+65536,D2),1000)
SET @D2 = @D2 + CASE WHEN @D2 < 0 THEN 65536 ELSE 0 END
SET @intVal = FLOOR(@D2/1000 + 70) ; PRINT(CAST(@intVal AS Datetime))
SET @intVal = @intVal * 365.25 ; PRINT(CAST(@intVal AS Datetime))
SET @intVal = FLOOR(@intVal + 0.75) ; PRINT(CAST(@intVal AS Datetime))
SET @intVal = @intVal + (@D2 % 1000) ; PRINT(CAST(@intVal AS Datetime))
SET @date = @intVal
SELECT @date
DECLARE @D2 INT
SET @D2= -27444
DECLARE @date DATETIME
--FLOOR(FLOOR(IF(D2<0,D2+65536,D2)/1000+70,1)*365.25+0.75,1)+MOD(IF(D2<0,D2+65536,D2),1000)
SET @D2 = @D2 + CASE WHEN @D2 < 0 THEN 65536 ELSE 0 END
-- SQL does integer division so first floor not needed
SET @date = FLOOR((@D2/1000 + 70) * 365.25 + 0.75) + (@D2 % 1000)
-- Above is the same result as this
--SET @date = FLOOR(FLOOR(@D2/1000 + 70) * 365.25 + 0.75) + (@D2 % 1000)
SELECT @date
DECLARE @D2 INT
SET @D2= -27444
DECLARE @date DATETIME
--FLOOR(FLOOR(IF(D2<0,D2+65536,D2)/1000+70,1)*365.25+0.75,1)+MOD(IF(D2<0,D2+65536,D2),1000)
SET @D2 = @D2 + CASE WHEN @D2 < 0 THEN 65535 ELSE 0 END
-- SQL does integer division so first floor not needed
SET @date = FLOOR((@D2/1000 + 70) * 365.25 + 0.75) + (@D2 % 1000) - 1
-- Above is the same result as this
--SET @date = FLOOR(FLOOR(@D2/1000 + 70) * 365.25 + 0.75) + (@D2 % 1000)
SELECT @date
Gregorian calendar from Julian day number
Let J be the Julian day number from which we want to compute the date components.
With J, compute a relative Julian day number j from a Gregorian epoch starting on March 1, 4800 (i.e. March 1, 4801 BC in the proleptic Gregorian Calendar), the beginning of the Gregorian quadricentennial 32,044 days before the epoch of the Julian Period.
With j, compute the number g of Gregorian quadricentennial cycles elapsed (there are exactly 146,097 days per cycle) since the epoch; subtract the days for this number of cycles, it leaves dg days since the beginning of the current cycle.
With dg, compute the number c (from 0 to 4) of Gregorian centennial cycles (there are exactly 36,524 days per Gregorian centennial cycle) elapsed since the beginning of the current Gregorian quadricentennial cycle, number reduced to a maximum of 3 (this reduction occurs for the last day of a leap centennial year where c would be 4 if it were not reduced); subtract the number of days for this number of Gregorian centennial cycles, it leaves dc days since the beginning of a Gregorian century.
With dc, compute the number b (from 0 to 24) of Julian quadrennial cycles (there are exactly 1,461 days in 4 years, except for the last cycle which may be incomplete by 1 day) since the beginning of the Gregorian century; subtract the number of days for this number of Julian cycles, it leaves db days in the Gregorian century.
With db, compute the number a (from 0 to 4) of Roman annual cycles (there are exactly 365 days per Roman annual cycle) since the beginning of the Julian quadrennial cycle, number reduced to a maximum of 3 (this reduction occurs for the leap day, if any, where a would be 4 if it were not reduced); subtract the number of days for this number of annual cycles, it leaves da days in the Julian year (that begins on March 1).
Convert the four components g, c, b, a into the number y of years since the epoch, by summing their values weighted by the number of years that each component represents (respectively 400 years, 100 years, 4 years, and 1 year).
With da, compute the number m (from 0 to 11) of months since March (there are exactly 153 days per 5-month cycle; however, these 5-month cycles are offset by 2 months within the year, i.e. the cycles start in May, and so the year starts with an initial fixed number of days on March 1, the month can be computed from this cycle by a Euclidian division by 5); subtract the number of days for this number of months (using the formula above), it leaves d days past since the beginning of the month.
The Gregorian date (Y, M, D) can then be deduced by simple shifts from (y, m, d).
*/
declare @j int
declare @g int
declare @dg int
declare @c int
declare @dc int
declare @b int
declare @db int
declare @a int
declare @da int
declare @y int
declare @m int
declare @d int
set @J = 2454558 -- Julian day number (rounded up)
set @j = 2454558 + 32044
set @g = @j / 146097
set @dg = @j % 146097
set @c = (@dg / 36524 + 1) * 3 / 4
set @dc = @dg - @c * 36524
set @b = @dc / 1461
set @db = @dc % 1461
set @a = (@db / 365 + 1) * 3 / 4
set @da = @db - @a * 365
set @y = @g * 400 + @c * 100 + @b * 4 + @a
set @m = (@da * 5 + 308) / 153 - 2
set @d = @da - (@m + 4) * 153 / 5 + 122
set @Y = @y - 4800 + (@m + 2) / 12
set @M = (@m + 2) % 12 + 1
set @D = @d + 1.5
select @d,@m,@y
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.