472,344 Members | 2,250 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,344 software developers and data experts.

Timestamp Conversion

I am working with an application that is returning a second based
timestamp. It returns values based on the 86440 second day in GMT.

Is there an ability within SQL Server to convert the value into an
hour time value?

I need to take this value and convert it to a hour value for the time
zone the client is in.


Jul 20 '05 #1
1 5425
Do you know exactly what your seconds-based timestamp represents? It sounds
like a count of the number of seconds since some base date. Unix systems for
example use timestamps based on a count of the number of seconds since
midnight on 1970-01-01. This can be converted to a DATETIME as follows:

SELECT DATEADD(SECOND,@ts_seconds,'19700101')

If you have a known date and time for one of your numeric timestamps it
shouldn't be too difficult to work out what the base date is. Just turn the
above function around. For example if you have a timestamp value that you
know is for 17:59 today, do this:

SELECT DATEADD(SECOND,-@ts_seconds,'2004-09-22T17:59:00') AS base_date

Once you have the base date you can easily convert any timestamp value.

David Portas
SQL Server MVP
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Ben | last post by:
I would like to use php to query a database and retrieve a unix timestamp. The problem is that mysql is storing the data in the date format and...
by: Stormblade | last post by:
Hey all, I have a web app that uses SQLServer 2000. I am switching to MySQL 4. 1.1. I have re-created all the tables but I'm running into 2...
by: Chris | last post by:
Hello, I'm having some problems right now with something that would seem to be rather simple (and probably is). But, the solution is escaping me...
by: Ulrich Wisser | last post by:
Hi, today I started to wonder about type conversion. I want to get all rows of a table dated between two given dates. Until now I use select *...
by: Jim C. Nasby | last post by:
Is there any reason why there isn't a predefined cast to go from a timestamp to a varchar? Is there a reason not to add one? -- Jim C. Nasby,...
by: Joseph Geretz | last post by:
I'm transferring a file from server to workstation, using Web Service Extensions and DIME. Along with the file, which travels as a DIME attachment,...
by: JJ | last post by:
How do I set one field to have the updated timestamp, and another to have the created timestamp? I want to do this directly from code generated...
by: kyosohma | last post by:
Hi, I am populating a mySQL database with data from the MS Access database. I have successfully figured out how to extract the data from Access,...
by: Ivan Velev | last post by:
Hello, Minimal example below - it gives me different output if I comment / uncomment the extra time.mktime call - note that this call is not...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.