■ DATETIME 값을 TICK 값으로 변환하는 방법을 보여준다.
▶ DATETIME → TICK 구하기 예제 (SQL)
|
1 2 3 4 5 6 7 8 9 |
SELECT dbo.GetTickCount('2014-03-05') AS TickCount; /* TickCount ------------------ 635295744000000000 */ |
▶ DATETIME → TICK 구하기 (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 |
CREATE FUNCTION dbo.Get365DayCount ( @SourceMonth INT ) RETURNS INT WITH SCHEMABINDING AS BEGIN RETURN CASE @SourceMonth WHEN 0 THEN 0 WHEN 1 THEN 31 WHEN 2 THEN 59 WHEN 3 THEN 90 WHEN 4 THEN 120 WHEN 5 THEN 151 WHEN 6 THEN 181 WHEN 7 THEN 212 WHEN 8 THEN 243 WHEN 9 THEN 273 WHEN 10 THEN 304 WHEN 11 THEN 334 WHEN 12 THEN 365 ELSE 0 END; END GO CREATE FUNCTION dbo.Get366DayCount ( @SourceMonth INT ) RETURNS INT WITH SCHEMABINDING AS BEGIN RETURN CASE @SourceMonth WHEN 0 THEN 0 WHEN 1 THEN 31 WHEN 2 THEN 60 WHEN 3 THEN 91 WHEN 4 THEN 121 WHEN 5 THEN 152 WHEN 6 THEN 182 WHEN 7 THEN 213 WHEN 8 THEN 244 WHEN 9 THEN 274 WHEN 10 THEN 305 WHEN 11 THEN 335 WHEN 12 THEN 366 ELSE 0 END; END GO CREATE FUNCTION dbo.GetDayCount ( @SourceYear INT ,@SourceMonth INT ) RETURNS INT WITH SCHEMABINDING AS BEGIN RETURN CASE WHEN (@SourceYear % 4 = 0) AND ((@SourceYear % 100 != 0) OR ((@SourceYear % 100 = 0) AND (@SourceYear % 400 = 0))) THEN dbo.Get366DayCount(@SourceMonth) ELSE dbo.Get365DayCount(@SourceMonth) END; END GO CREATE FUNCTION dbo.GetTimeTickCount ( @SourceHour INT ,@SourceMinute INT ,@SourceSecond INT ) RETURNS BIGINT WITH SCHEMABINDING AS BEGIN RETURN (((@SourceHour * 3600) + CONVERT(BIGINT, @SourceMinute) * 60) + CONVERT(BIGINT, @SourceSecond)) * 10000000; END GO CREATE FUNCTION dbo.GetDateTickCount ( @SourceYear INT ,@SourceMonth INT ,@SourceDay INT ) RETURNS BIGINT WITH SCHEMABINDING AS BEGIN RETURN CONVERT ( BIGINT, ( ( ( ( ( ((@SourceYear - 1) * 365) + ((@SourceYear - 1) / 4) ) - ((@SourceYear - 1) / 100) ) + ((@SourceYear - 1) / 400) ) + dbo.GetDayCount(@SourceYear, @SourceMonth - 1) ) + @SourceDay ) - 1 ) * 864000000000; END GO CREATE FUNCTION dbo.GetTickCount ( @SourceDateTime DATETIME ) RETURNS BIGINT WITH SCHEMABINDING AS BEGIN RETURN dbo.GetDateTickCount(DATEPART(yyyy, @SourceDateTime), DATEPART(mm, @SourceDateTime), DATEPART(dd, @SourceDateTime)) + dbo.GetTimeTickCount(DATEPART(hh , @SourceDateTime), DATEPART(mi, @SourceDateTime), DATEPART(ss, @SourceDateTime)) + (CONVERT(BIGINT, DATEPART(ms, @SourceDateTime)) * CONVERT(BIGINT, 10000)); END GO |