SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


need help with applying function within Query


need help with applying function within Query

Author
Message
tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
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
Attachments
query.png (14 views, 57.00 KB)
udfTicksToDateTime.zip (7 views, 99.00 KB)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25797 Visits: 17509
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
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


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25797 Visits: 17509
tigars39 (7/30/2013)
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).

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
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


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25797 Visits: 17509
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tigars39
tigars39
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 29
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 @Days BIGINT
DECLARE @DaysBefore1753 BIGINT
DECLARE @TimeTicks BIGINT
DECLARE @Seconds BIGINT

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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25797 Visits: 17509
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 @Days BIGINT
DECLARE @DaysBefore1753 BIGINT
DECLARE @TimeTicks BIGINT
DECLARE @Seconds BIGINT

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27908 Visits: 39921
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 10340
I reckon it's somewhere around your DOB, Lowell :-P

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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search