Very interesting. After diggin a litle more into that SQL database i found that the 2 user defined function i attached on my first post are being called by a TRIGGER :

USE [SMS_AR_Report]

GO

/****** Object: UserDefinedFunction [dbo].[udfGetLoggedItemsInsertion_DateT] Script Date: 07/30/2013 17:03:17 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfGetLoggedItemsInsertion_DateT]

(

@Ticks BIGINT, --The sample time in ticks

@SampleTypeID INT, --The sample type id of data to be inserted

@DateTimeNow DATETIME --The current date time, getdate does not work in udf

)

RETURNS DATETIME AS

BEGIN

--Copyright ¼ 2006 Square D / Schneider Electric. All rights reserved.

--If the sample type is logged data, we'll start with the sample date

Declare @ReturnDate as DateTime

IF @SampleTypeID <> -1

BEGIN

set @ReturnDate = dbo.udfTicksToDateTime(@Ticks)

END

ELSE

BEGIN

--If it is archived data (sample type of -1) then

--we want to start with the current date that is passed in

set @ReturnDate = @DateTimeNow

END

--Now modify the date to point to midnight of the first day of the next month.

--This ensures that ALL data for the given month will remain in the

--database for at least as long as the retention period. Some data will

--remain longer than the retention period (but by no more than a month).

--If we don't do this, then some data may be archived out almost

--immediately after it is imported.

set @ReturnDate = convert(nvarchar(25),Month(@ReturnDate)) + '/1/' + convert(nvarchar(25),Year(@ReturnDate))

set @ReturnDate = DateAdd(Month, 1, @ReturnDate)

RETURN @ReturnDate

END

GO

/****** Object: UserDefinedFunction [dbo].[udfTicksToDateTime] Script Date: 07/30/2013 17:03:17 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfTicksToDateTime]

(

@Ticks BIGINT

)

RETURNS DATETIME AS

BEGIN

DECLARE @DaysBIGINT

DECLARE @DaysBefore1753BIGINT

DECLARE @TimeTicksBIGINT

DECLARE @SecondsBIGINT

IF @Ticks < CONVERT(BIGINT,624511296000000000)

BEGIN

SET @Ticks = CONVERT(BIGINT,624511296000000000)

END

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

GO

/****** Object: Table [dbo].[HST_Currents] Script Date: 07/30/2013 17:03:17 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[HST_Currents](

[Timestamp_ID] [bigint] NOT NULL,

[Device_ID] [int] NOT NULL,

[Topic_ID] [int] NOT NULL,

[Sample_Type_ID] [smallint] NOT NULL,

[Original_Value] [float] NOT NULL

) ON [PRIMARY]

GO

/****** Object: Trigger [TRIG_UPDATE_HST_Currents] Script Date: 07/30/2013 17:03:18 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE TRIGGER [dbo].[TRIG_UPDATE_HST_Currents] ON [dbo].[HST_Currents] FOR UPDATE AS IF UPDATE(ORIGINAL_VALUE) BEGIN INSERT INTO PMO_HEVENTS (TIMESTAMP_ID, DEVICE_ID, TOPIC_ID, SAMPLE_TYPE_ID, VALUE, ACTION) SELECT TIMESTAMP_ID, DEVICE_ID, TOPIC_ID, SAMPLE_TYPE_ID, ORIGINAL_VALUE, 0 FROM DELETED INSERT INTO PMO_HEVENTS (TIMESTAMP_ID, DEVICE_ID, TOPIC_ID, SAMPLE_TYPE_ID, VALUE, ACTION) SELECT TIMESTAMP_ID, DEVICE_ID, TOPIC_ID, SAMPLE_TYPE_ID, ORIGINAL_VALUE, 1 FROM INSERTED END

GO

/****** Object: UserDefinedFunction [dbo].[GetTopicGroupID] Script Date: 07/30/2013 17:03:18 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

--Copyright ¬ 2006 Square D / Schneider Electric. All rights reserved.

CREATE FUNCTION [dbo].[GetTopicGroupID] (@Topic_IDint)

RETURNS int AS

BEGIN

DECLARE @GroupIDint

SELECT @GroupID = T2.GROUP_ID

FROM TOPIC_GROUPS T1,

PARTITIONED_GROUPS T2

WHERE T1.GROUP_ID = T2.GROUP_ID

ANDT1.TOPIC_ID = @Topic_ID

IF @GroupID = null

SET @GroupID = -1

RETURN @GroupID

END

GO

/****** Object: Trigger [TRIG_INSTEADOFINSERT_HST_Currents] Script Date: 07/30/2013 17:03:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE TRIGGER [dbo].[TRIG_INSTEADOFINSERT_HST_Currents] ON [dbo].[HST_Currents] INSTEAD OF INSERT AS BEGIN

-- Use left outer join to select all items in the INSERTED table that are not in the main historical table.

-- This enforces a sort of virtual primary key on the timestamp_id, device_id, and topic_id fields. This

-- INSTEAD OF trigger runs in place of the actual INSERT that was executed on the table. When the

-- INSERT statement below runs, it then triggers any AFTER INSERT triggers that are also set up for

-- this table.

DECLARE @Device_IDINT

DECLARE @Topic_IDINT

DECLARE @Timestamp_IDBIGINT

DECLARE @Sample_Type_IDSMALLINT

DECLARE@Original_Value FLOAT

DECLARE InsertList_HST_Currents CURSOR FOR SELECT * FROM INSERTED

OPEN InsertList_HST_Currents

--Get first Entry.

FETCH NEXT FROM InsertList_HST_Currents INTO @Timestamp_ID, @Device_ID, @Topic_ID, @Sample_Type_ID, @Original_Value

WHILE @@FETCH_STATUS = 0

BEGIN

IF (SELECT COUNT(*) FROM HST_Currents WHERE @Timestamp_ID = Timestamp_ID AND

@Device_ID = Device_ID AND @Topic_ID = Topic_ID) = 0

BEGIN

INSERT INTO HST_Currents VALUES (@Timestamp_ID, @Device_ID, @Topic_ID, @Sample_Type_ID, @Original_Value)

END

FETCH NEXT FROM InsertList_HST_Currents

INTO @Timestamp_ID, @Device_ID, @Topic_ID, @Sample_Type_ID, @Original_Value

END

CLOSE InsertList_HST_Currents

DEALLOCATE InsertList_HST_Currents

END

GO

/****** Object: Trigger [TRIG_AFTERINSERT_HST_Currents] Script Date: 07/30/2013 17:03:18 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE TRIGGER [dbo].[TRIG_AFTERINSERT_HST_Currents] ON [dbo].[HST_Currents] AFTER INSERT AS

BEGIN

INSERT INTO LOGGEDITEMS (DEVICE_ID,TOPIC_ID,YEARMONTH,SAMPLE_TYPE_ID,TABLENAME,GROUP_ID,INSERTION_DATET)

SELECT DEVICE_ID,TOPIC_ID,(YEAR(dbo.udfTicksToDateTime(TIMESTAMP_ID))*100+MONTH(dbo.udfTicksToDateTime(TIMESTAMP_ID))),SAMPLE_TYPE_ID,'HST_Currents',(dbo.GetTopicGroupID(TOPIC_ID)),(dbo.udfGetLoggedItemsInsertion_DateT (TIMESTAMP_ID, SAMPLE_TYPE_ID,GETUTCDATE()))

FROM INSERTED

END

GO

/****** Object: ForeignKey [FK_HST_Currents_Devices] Script Date: 07/30/2013 17:03:17 ******/

ALTER TABLE [dbo].[HST_Currents] WITH NOCHECK ADD CONSTRAINT [FK_HST_Currents_Devices] FOREIGN KEY([Device_ID])

REFERENCES [dbo].[Devices] ([local_Device_ID])

NOT FOR REPLICATION

GO

ALTER TABLE [dbo].[HST_Currents] CHECK CONSTRAINT [FK_HST_Currents_Devices]

GO

/****** Object: ForeignKey [FK_HST_Currents_Topics] Script Date: 07/30/2013 17:03:17 ******/

ALTER TABLE [dbo].[HST_Currents] WITH NOCHECK ADD CONSTRAINT [FK_HST_Currents_Topics] FOREIGN KEY([Topic_ID])

REFERENCES [dbo].[Topics] ([topic_ID])

NOT FOR REPLICATION

GO

ALTER TABLE [dbo].[HST_Currents] CHECK CONSTRAINT [FK_HST_Currents_Topics]

GO

The part that seems intersting is:

(YEAR(dbo.udfTicksToDateTime(TIMESTAMP_ID))*100+MONTH(dbo.udfTicksToDateTime(TIMESTAMP_ID)

that code i run each time a new record is added to the table. Anyone here can help me on how to use this information to be able to convert the Timestamp_ID column to a DATETIME?

thanks