July 30, 2013 at 7:48 am
Hi all
Its my first post here! Glad to be here.Hopefully someone can help me with the following.
If you look at the attach screenshot, you will see the 3 SQL table i am working with along with the query i will need to run on these tables.
The problem is that in the "CuRRENTS" table, the first column is in "Ticks" format instead of DATETIME! so in my query i need to get the values from that column (Timestamp_ID) to DATETIME format. Now by looking at theat SQL Database, i saw 2 fucntions (see attached notepads) that may have been created to do exactly that: converting that column into DATETIME. The problem is that i dont know how i would use them into my query.
Is there anyone that could help?
thanks in advance
July 30, 2013 at 8:11 am
Hi and welcome to SSC. Myself and countless others are willing and able to help you. However from what you posted we don't have much to work with. It would really help if you could post a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2013 at 8:34 am
Hi
thanks for the reply. I checkd the link in your signature and, am sorry to say that i dont understand how to do that.
I am working on an existing SQL database so the 3 tables i am querying are already existing. (see in previously posted attachments).
if i click on EDIT on the existing table i get:
USE [SMS_AR_Report]
GO
/****** Object: Table [dbo].[HST_Currents] Script Date: 07/30/2013 10:26:47 ******/
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
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
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]
All i need help with is How do i modify the followiing query to convert the column "Timestamp_ID" into DATETIME format :
SELECT Devices.name, Topics.short_name, Topics.name AS PointName, Topics.short_units, Topics.description, HST_Currents.Original_Value,
HST_Currents.Timestamp_ID
FROM Devices INNER JOIN
HST_Currents ON Devices.local_Device_ID = HST_Currents.Device_ID INNER JOIN
Topics ON HST_Currents.Topic_ID = Topics.topic_ID
July 30, 2013 at 8:46 am
tigars39 (7/30/2013)
Hithanks for the reply. I checkd the link in your signature and, am sorry to say that i dont understand how to do that.
I am working on an existing SQL database so the 3 tables i am querying are already existing. (see in previously posted attachments).
All i need help with is How do i modify the followiing query to convert the column "Timestamp_ID" into DATETIME format :
The problem is that I have no idea what the data looks like in that table. Thanks for posting the ddl for the table, at least now I know that it is a bigint.
What you are running into here is one of my biggest pet peeves in sql. So many people will put datetime data into an int/bigint column. This is really bad practice. There is no way to do validation and date calculations become a lot more difficult than they need to be. If a date is what is being it should be stored in a datetime column.
Now that I have a table definition so I can have the table on my database can you create some insert statements for me that represents the data found in your table?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2013 at 9:28 am
Sorry for the delay. I had to figure out how to generate insert statements for existing database here what i got:
USE [SMS_AR_Report];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO [dbo].[HST_Currents]([Timestamp_ID], [Device_ID], [Topic_ID], [Sample_Type_ID], [Original_Value])
VALUES (634956975000000000, 11, 1004, 2021, 27.9),
(634956975000000000, 6, 1004, 2021, 165.5),
(634956975000000000, 5, 1004, 2021, 95.5),
(634956975000000000, 2, 1004, 2021, 132.9),
(634956975000000000, 16, 1004, 2021, 10),
(634956975000000000, 18, 1004, 2021, 37),
(634956975000000000, 17, 1004, 2021, 34),
(634956975000000000, 14, 1004, 2021, 43),
(634956975000000000, 13, 1004, 2021, 34),
(634956975000000000, 12, 1004, 2021, 28),
(634956975000000000, 24, 1004, 2021, 11.8),
(634956975000000000, 23, 1004, 2021, 217.9),
(634956975000000000, 26, 1004, 2021, 199.5),
(634956975000000000, 25, 1004, 2021, 69.2),
(634956975000000000, 21, 1004, 2021, 105.8),
(634956975000000000, 39, 1004, 2021, 33),
(634956975000000000, 40, 1004, 2021, 284.1),
(634956975000000000, 38, 1004, 2021, 12.1),
(634956975000000000, 37, 1004, 2021, 0),
(634956975000000000, 30, 1004, 2021, 170.8)
COMMIT;
RAISERROR (N'[dbo].[HST_Currents]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
July 30, 2013 at 9:34 am
OK so you have a bigint with a value of "634956975000000000" in every row. What does this value represent? It looks nothing like a datetime value.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2013 at 9:48 am
That column is the tIME STAMP (date and time) that a sample of a reading have been taken. I just genertaed insert statement for the first few rows because that table contains thousand of rows! the values are differents in other rows for the TimeStamp_ID Also as i said, i also found a scalar function part of the database wich is as follow:
/****** Object: UserDefinedFunction [dbo].[udfTicksToDateTime] Script Date: 07/30/2013 11:43:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
i think that is the fucntion used to convert the ticks to datetime. i just dont know how i can use it in my query to convert back to datetime
July 30, 2013 at 9:53 am
tigars39 (7/30/2013)
That column is the tIME STAMP (date and time) that a sample of a reading have been taken. I just genertaed insert statement for the first few rows because that table contains thousand of rows! the values are differents in other rows for the TimeStamp_ID Also as i said, i also found a scalar function part of the database wich is as follow:
/****** Object: UserDefinedFunction [dbo].[udfTicksToDateTime] Script Date: 07/30/2013 11:43:35 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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
i think that is the fucntion used to convert the ticks to datetime. i just dont know how i can use it in my query to convert back to datetime
When you say TimeStamp what do you mean? Where do those values come from? TimeStamp is a datatype in sql server, I don't think you mean that. I get the impression that is a value that represents something but I have no idea what it is. Maybe it is the number of seconds since 1/1/1900? Give me some idea of what the number means and I can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2013 at 11:06 am
wow just some wild guesses here, no idea if it is right:
i'm assuming ticks means what i found on my search internet search:
declare @ticks bigint = 624511296000000000;
--One Tick is 10 ns. So one second is 10 million ticks
select @ticks = @ticks / 10000000;
--60 seconds to a minute, 60 minutes ot an hour
select @ticks = @ticks / (60 * 60);
--add those minutes to the min date of SQL server?
select DATEADD(hh,@ticks,'1900-01-01')
--3878-12-31 00:00:00.000
--maybe that date is from 0 BC?
select DATEADD(year,-1900,DATEADD(hh,@ticks,'1900-01-01'))
--1978-12-31 00:00:00.000
Lowell
July 30, 2013 at 1:31 pm
I reckon it's somewhere around your DOB, Lowell 😛
SELECT *
FROM (SELECT ticks = 624511296000000000) z
CROSS APPLY (SELECT microseconds = z.ticks/100) a
CROSS APPLY (SELECT milliseconds = a.microseconds/1000) b
CROSS APPLY (SELECT [seconds] = b.milliseconds/1000) c
CROSS APPLY (SELECT [minutes] = c.[seconds] / 60) d
CROSS APPLY (SELECT [hours] = d.[minutes] / 60) e
CROSS APPLY (SELECT [days] = e.[hours] / 24) f
CROSS APPLY (SELECT startdate = DATEADD(DD,0-f.[days],GETDATE())) g
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 30, 2013 at 3:13 pm
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
July 30, 2013 at 3:56 pm
tigars39 (7/30/2013)
Anyone here can help me on how to use this information to be able to convert the Timestamp_ID column to a DATETIME?
You have three people on this thread that are willing. What we don't have is enough details to help you. You have been presented with a number of guesses. I assume that none of those are correct? Give us something that we can work with. Find a value for this Timestamp_ID AND what the corresponding datetime should be.
Here is what we know. We have a bigint with some very large values. You want us to help you turn that into a datetime. We have no idea how these large values were derived so we have little to no chance of deciphering it correctly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2013 at 1:57 pm
Based on the function that you posted the following should convert your ticks.
You may want to case statement to make sure the timestamp_id is at least 624511296000000000 to match the function.
SELECT t.Timestamp_id
, DATEADD(
s,
(t.Timestamp_id % CONVERT(BIGINT,864000000000)) / CONVERT(BIGINT,10000000),
DATEADD(
d,
(t.Timestamp_id / CONVERT(BIGINT,864000000000)) - CONVERT(BIGINT,639905),
'17530101'
)
)
FROM (SELECT Timestamp_id FROM (VALUES (624511296000000000),(634956975000000000)) AS Ticks(Timestamp_id)) T
From the look of it the ticks have a datum of year 0.
Given that it appears the 19800101 is the least date that the function will return, you could simplify this further to
SELECT DATEADD(s,
(t.Timestamp_id - 624511296000000000) / 10000000,
'19800101')
FROM (SELECT Timestamp_id FROM (VALUES (624511296000000000),(634956975000000000)) AS Tick(Timestamp_id)) T
August 1, 2013 at 6:28 am
Sean: dont know my friend. I was still trying to find more infos about the Timestamp_ID.
Thanks for the hints Lowell!
Chris, i think thats what i needed my friend!. Now the question is,(sorry for the noob question) how do i insert your conversion code into the following query:
SELECT HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description,
HST_Currents.Original_Value
FROM Devices INNER JOIN
HST_Currents ON Devices.local_Device_ID = HST_Currents.Device_ID INNER JOIN
LoggedItems ON Devices.local_Device_ID = LoggedItems.Device_ID INNER JOIN
Topics ON HST_Currents.Topic_ID = Topics.topic_ID AND LoggedItems.Topic_ID = Topics.topic_ID
August 1, 2013 at 6:47 am
Sorry i meant Micky 🙂
thanks in advance
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply