 |
|
|
In most cases rounding down to the nearest millisecond will suffice.
I usually create these sql functions:
CREATE FUNCTION
[dbo].[DateDiffInTicks] (@StartDate DATETIME, @EndDate DATETIME)
RETURNS
BIGINT
AS BEGIN
DECLARE @ms BIGINT = DATEDIFF(ms, @StartDate, @EndDate)
RETURN @ms * 10000
END
GO
CREATE FUNCTION
[dbo].[AddTicksToDateTime] (@DateTime DATETIME, @TimeSpan BIGINT)
RETURNS
DATETIME
AS BEGIN
DECLARE @ms BIGINT = CAST(@TimeSpan / 10000 AS BIGINT)
RETURN DATEADD(ms,@ms, @DateTime)
END
GO
|
|
|
|
|
|
|
i encountered the problem like that.. i have a sproc that returns a date column
and when im assigning that column to a .Net object's field(datetime type also),
the value was somewhat converted to another datetime format.
my solution to that problem was that i cast the datetime column from the db into a string
like this;
SELECT CONVERT(varchar,getutcdate(),121) as 'DATEWITHMILLISECONDS'
and on the clientside/MT side, i change the datetime field into a simple string,
so that the returned column will be stored as is..
it is fast and don't to much processing in the serverside (it's delegated to the client)
that's my implementation and it's working fine for me.. 
Brute Force!
|
|
|
|
|
|
|
This has come in very handy for fixing my database. I was storing .NET DateTime as a datetime, before realising I was losing some of the milliseconds.
I'm now storing the .NET DateTime as a bigint (ticks), but needed to convert all my old data. This worked perfectly.
|
|
|
|
|
|
|
Article really should list the code rather than solely provide a download.
|
|
|
|
|
|
|
CREATE FUNCTION [dbo].[MonthToDays365] (@month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for non-leap years
BEGIN
RETURN
CASE @month
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].[MonthToDays366] (@month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for leap years
BEGIN
RETURN
CASE @month
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].[MonthToDays] (@year int, @month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for non-leap years
BEGIN
RETURN
-- determine whether the given year is a leap year
CASE
WHEN (@year % 4 = 0) and ((@year % 100 != 0) or ((@year % 100 = 0) and (@year % 400 = 0))) THEN dbo.MonthToDays366(@month)
ELSE dbo.MonthToDays365(@month)
END
END
GO
CREATE FUNCTION [dbo].[TimeToTicks] (@hour int, @minute int, @second int)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given hour/minute/second to the corresponding ticks
BEGIN
RETURN (((@hour * 3600) + CONVERT(bigint, @minute) * 60) + CONVERT(bigint, @second)) * 10000000
END
GO
CREATE FUNCTION [dbo].[DateToTicks] (@year int, @month int, @day int)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given year/month/day to the corresponding ticks
BEGIN
RETURN CONVERT(bigint, (((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + dbo.MonthToDays(@year, @month - 1)) + @day) - 1) * 864000000000;
END
GO
CREATE FUNCTION [dbo].[DateTimeToTicks] (@d datetime)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given datetime to .NET-compatible ticks
-- see http:BEGIN
RETURN
dbo.DateToTicks(DATEPART(yyyy, @d), DATEPART(mm, @d), DATEPART(dd, @d)) +
dbo.TimeToTicks(DATEPART(hh, @d), DATEPART(mi, @d), DATEPART(ss, @d)) +
(CONVERT(bigint, DATEPART(ms, @d)) * CONVERT(bigint,10000));
END
GO
|
|
|
|
|
|
|
hey... the other way around
The functions accepts the ticks and return a Date
Not sure when I copied it from... but it works
CREATE FUNCTION dbo.GetTimeFromTicks
(
@Ticks BIGINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Days BIGINT
DECLARE @DaysBefore1753 BIGINT
DECLARE @TimeTicks BIGINT
DECLARE @Seconds BIGINT
SET @Days = @Ticks / CONVERT(BIGINT,864000000000)
SET @DaysBefore1753 = CONVERT(BIGINT,639905)
SET @TimeTicks = @Ticks % CONVERT(BIGINT,864000000000)
SET @Seconds = @TimeTicks / CONVERT(BIGINT,10000000)
RETURN DATEADD(s,@Seconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))
END
Ricardo Casquete
|
|
|
|
|
|
|
Before I saw your post here, I was using other solution...
You can also do somethin like that (just an idea) - result should be the same:
select (DATEDiff(s, '20060823', [...some datetime date here...]) + 63291888000 ) * 10000000
for example: select (DATEDiff(s, '20060823', getdate()) + 63291888000 ) * 10000000
Greetings,
Greg
|
|
|
|
|
|
|
could you please explain why you have 20060823 and 63291888000
|
|
|
|
|
|
|
it would also be useful to be able to go the other way (convert ticks to datetime)
|
|
|
|
|
|
|
nmrs2 wrote: it would also be useful to be able to go the other way (convert ticks to datetime)
The reverse can be found here
http://dotnetjunkies.com/WebLog/jdixon/archive/2004/05/21/14228.aspx
Shamelessly copied, author is Jerry Dixon:
CREATE FUNCTION dbo.udfTicksToDateTime
(
@Ticks BIGINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Days BIGINT
DECLARE @DaysBefore1753 BIGINT
DECLARE @TimeTicks BIGINT
DECLARE @Seconds BIGINT
SET @Days = @Ticks / CONVERT(BIGINT,864000000000)
SET @DaysBefore1753 = CONVERT(BIGINT,639905)
SET @TimeTicks = @Ticks % CONVERT(BIGINT,864000000000)
SET @Seconds = @TimeTicks / CONVERT(BIGINT,10000000)
RETURN DATEADD(s,@Seconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))
END
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin