-1

I have a database with integer fields (columns) named fSystemDate, fOpenned, fStatusDate, etc... I think they represent dates, but I don't know their format. The values in those fields are how these: 76505, 76530, 76554, 76563. I do not have examples with the real date associated with them.

Solved. See answers.

Ricardo Julian Basto Navarro
  • 81
  • 1
  • 6
  • When you figure out the format, then we can talk about conversion. At the very least, you need examples of dates with the real date associated with them. Also, I removed the exrraneous database tagas. – Gordon Linoff Jan 08 '16 at 23:42
  • These 5 digits look like date serials, i.e. a continuous count of days since some defined epoch. For example, Modified Julian Day (MJD) numbers count from midnight, Nov 18, 1858. The MJD for noon, Sept 7, 2015 (Labor Day) is 57272.5. Your date serial clearly has a different epoch definition. – scottb Jan 09 '16 at 00:01
  • I hope, Someone who stored this numbers added a scalar-valued function to your db to take back this numbers to datetime :) – halit Jan 09 '16 at 00:10
  • Thanks for your comments. Look for more information about date serials. – Ricardo Julian Basto Navarro Jan 09 '16 at 00:15

2 Answers2

3

I found that this format is part of a programming language called Clarion and his date numbering starts at the date 28-December-1800. I can convert clarion data to sql date in two ways:

  1. SELECT DATEADD(day, 76505, '28-12-1800')

    where the result would be 2010-06-15 00:00:00.

  2. SELECT CONVERT(DateTime,76505 - 36163)

    where the result is same. The number 36163 is used to adjust a SQL. This is the number of days between 1-Jan-1900 (MSSQL datetime numbering starts) and 28-Dec-1800 (Clarion datetime numbering starts).

The result in my case is correct because I asked them (my customer) examples of data from your application and compare information.

Ricardo Julian Basto Navarro
  • 81
  • 1
  • 6
0

It's rather hard to help you given just a number. It looks like your dates are some sort of serial number. But without any other data points

  • epoch. An epoch is the zero point of a calendrical system.

  • increment. How big is a tick in the serial number? 1 day? 1 hour, 1 minute? A week? A month?

  • source hardware/operating system. From what computer system did the value originate? Different systems represent dates differently, using different calendrical systems with different epochs.

  • source software system. What software created the value? Was it custom software? What language what it written in? When? What is the backing store for the data? Databases, filesystems, etc., might all have their own internal date representation.

  • the represented value. If 76563 is indeed a representation of a date, what date does it represent? Or at least, does it represent a recent date? a date in the past? a date in the future?

It's impossible to answer your question. This page might help you:

http://www.itworld.com/article/2823335/data-center/128452-Just-dating-The-stories-behind-12-computer-system-reference-dates.html

It lists some common epochs for different computer systems:

Edited to note: here's one data point for you: Adding 76,563 days to 1 Jan 1800 yields the date 16 August 2009.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
  • I apologize for the lack of information, I do not know these details, I thought that someone could make that kind of representation of dates and that is why ask in the forum.Thank you very much for your comments and for the link, it looks excellent. – Ricardo Julian Basto Navarro Jan 09 '16 at 00:30
  • Hi. I was researching more about my problem, I found that this format belongs to a programming language called Clarion. The base date of Clarion is the 12/28/1800. Many thanks for your help. – Ricardo Julian Basto Navarro Jan 12 '16 at 16:51