need help with applying function within Query

Question

need help with applying function within Query

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

tigars39 SSC Enthusiast Points: 120 More actions · Answer 1

aslo i forgot to say that i am using SQL2005 so the VALUES heyword seem to be a problem in that version. WHat would be the equivalent code for SQL2005?

thanks again

mickyT SSChampion Points: 10360 More actions · Answer 2

tigars39 (8/1/2013)
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

Just add it into the select clause

SELECT

HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description,

HST_Currents.Original_Value,

DATEADD(s,(HST_Currents.Timestamp_id - 624511296000000000) / 10000000,'19800101') AS TimeStampDateTime

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

tigars39 SSC Enthusiast Points: 120 More actions · Answer 3

Works Great!!!:cool: Thanks much MickyT!!!

now one more step. :w00t:

i need the converted date to be in the format: yyyy-mm-ddThh:mm:ss.mmm

i found this using google:

SELECT convert(varchar, getdate(), 126)

but still trying to figure out how to include that into my query which is now:

SELECT

HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description,

HST_Currents.Original_Value,

DATEADD(s,(HST_Currents.Timestamp_id - 624511296000000000) / 10000000,'19800101') AS TimeStampDateTime

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

thznks again

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 4

Try this:

SELECT

HST_Currents.Timestamp_ID, Devices.name, Topics.short_name, Topics.name AS Expr1, Topics.short_units, Topics.description,

HST_Currents.Original_Value,

convert(varchar, DATEADD(s,(HST_Currents.Timestamp_id - 624511296000000000) / 10000000,'19800101'), 126) AS TimeStampDateTime

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

tigars39 SSC Enthusiast Points: 120 More actions · Answer 5

tigars39

SSC Enthusiast

Points: 120

August 2, 2013 at 2:01 pm

#1638338

thanks Laurie! 🙂