Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to convert 9 digit number in SQL database to a date mm/dd/yyyy and time (HH:MM AM/PM)

Posted on 2012-08-14
5
Medium Priority
?
2,390 Views
Last Modified: 2012-08-14
I have a database of records from keys. the tiem and date records are stored in 9-digit values that are all in the future, but, when i pull a report using the software, the same number of records show up with the correct date and time. So I am wondering is someone could help me use excel to convert this:

471895438

into something like this:

9/29/2010 10:05:26 AM

Thank you
0
Comment
Question by:speeDemon
  • 3
5 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38294305
I do not see the relationship between the two numbers given above. Can you supply a few more coded dates with the corresponding real date/time?
0
 
LVL 1

Author Comment

by:speeDemon
ID: 38294327
Sure - now based on what I see in the report generated from the software and the database these entries seem to be replated:

9/29/2010 10:05:28 AM - 465325528
9/29/2010 10:05:30 AM - 465325530

here is the email reply i just got from the support email address
"That value represents the number of seconds since 1996 (January 1st) in UTC.  So, 524510194 seconds starting from 1996 would be: 8/14/2012 5:16:34 PM UTC.  Then, you need to adjust for the current timezone.  Pacific Time is currently -7 UTC, which takes you to the date you referenced, 8/14/2012 10:16:34 AM."
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1000 total points
ID: 38294379
Try this formula

=35065+A1/86400-7/24

where 35065 represents 1996,
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38294381
You will have to format the result in the date/time format
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 38294382
Here is a function that will achieve this for you
Function ConvertDateSerial(serialDate As Long) As Date
  ConvertDateSerial = CDate(serialDate / 86400 + 35065)
End Function

Open in new window

where 86400 is seconds per day
and 35065 is days from 1/1/1900 (excel serial date start) and 1/1/1996

Michael
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question