Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

need help with applying function within Query Expand / Collapse
Author
Message
Posted Tuesday, July 30, 2013 7:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
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




  Post Attachments 
query.png (8 views, 57.58 KB)
udfTicksToDateTime.zip (4 views, 99.23 KB)
Post #1479010
Posted Tuesday, July 30, 2013 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
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 Moden's 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)
Post #1479024
Posted Tuesday, July 30, 2013 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
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

Post #1479040
Posted Tuesday, July 30, 2013 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
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 Moden's 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)
Post #1479044
Posted Tuesday, July 30, 2013 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
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

Post #1479071
Posted Tuesday, July 30, 2013 9:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
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 Moden's 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)
Post #1479075
Posted Tuesday, July 30, 2013 9:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 7:39 AM
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
Post #1479084
Posted Tuesday, July 30, 2013 9:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 13,085, Visits: 12,551
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 Moden's 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)
Post #1479087
Posted Tuesday, July 30, 2013 11:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1479110
Posted Tuesday, July 30, 2013 1:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 1,033, Visits: 6,802
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




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
Post #1479145
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse