February 12, 2014 at 2:04 pm
I know this is a common question and scenario and trust me, I've tried to do my homework before posting. Haven't had much luck which is why I'm asking. Here goes.
I am getting data from large, mainframe database. There are a couple of fields which I believe store date information and date time information.
First field is known as LAST_UPDT_TIME_STAMP which as the name implies, seems to hold data showing when the last time the record was updated. It contains values such as:
635584812882
635584812848 and so on.
I've tried many ways but cannot seem to convert this to a date, time, or DateTime. I've tried Cast & Convert. Any pointers??
Second field is known as LOC_LIC_EXP_REV_DTE which supposedly holds data to "append" or update a filed which is properly showing a date. Let's call it LIC_EXP_DATE.
LOC_LIC_EXP_REV_DTE is supposed to show a "revised" date for LIC_EXP_DATE. It contains values such as:
79858768
79858768
Just like the first example....any pointers on converting this over to a DateTime I can use??
February 12, 2014 at 2:18 pm
To convert to a date, you need to be able to specify the format of the dates you are sending in. And it does not sound like you know what the format is. Is there no documentation for what those numbers mean?
February 12, 2014 at 2:24 pm
I do not but I can ask the "mainframe" guys. Thanks for the help.....
February 12, 2014 at 3:42 pm
You need to know at what date and time the reference data is, meaning something like '1960-01-01 00:00:00' and that bigint represents the number of seconds that has passed since that bigint was inserted.
This code works for the data that I am dealing with, you may need to modify it for your situation.
SELECT
DATEADD(s,CAST(<BigIntColumn> AS BIGINT) / 1000000, '1960-01-01 00:00:00') AS ConvertBigIntToDate
I am going thru the same thing, and it is really difficult, and what is now considered antiquated way of storing a date value.
Andrew SQLDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply