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
Medium Priority
2,390 Views
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
Question by:speeDemon
• 3

LVL 43

Expert Comment

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

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

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

ID: 38294381
You will have to format the result in the date/time format
0

LVL 23

Expert Comment

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
``````
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

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.
###### Suggested Courses
Course of the Month21 days, 1 hour left to enroll