# 9 digit date number (ex.01.01.2014 => 131989761)

### Question

• Hello

A program saves date data in SQL like this

01.01.2014   =>    131989761
01.02.2014 => 131990017

and I want to use this data in excel but I can't convert it normal date format in excel.

Berk

Sunday, April 13, 2014 9:27 AM

• every day it increases 1 unit
every mount it increases 256 unit
and for every year it increases 65536 unit.

8 bits for day; 8 bits for month.

date to number
number = (day * 2⁰) + (month * 2⁸) + (year * 2¹⁶)

number to date
day = number mod 2⁸;
month = number div 2⁸ mod 2⁸;
year = number div 2¹⁶;

`-- Excell / Calc=DATE( QUOTIENT(B12; 65536); MOD( QUOTIENT(B12; 256); 256); MOD(B12 ;256) )`

```-- code #1 v4  - number to date
declare @DateValue int, @DateDMY date;

--
set @DateValue= 131989761;
set @DateDMY= Convert(date, convert(varchar, (@DateValue % 256)) + '/' +
convert(varchar, (@DateValue / 256 % 256)) + '/' +
convert(varchar, (@DateValue / 65536)), 103)
SELECT @DateValue, @DateDMY;

--
set @DateValue= 131990017;
set @DateDMY= Convert(date, convert(varchar, (@DateValue % 256)) + '/' +
convert(varchar, (@DateValue / 256 % 256)) + '/' +
convert(varchar, (@DateValue / 65536)), 103)
SELECT @DateValue, @dateDMY;```

```-- code #2 v3  - date to number
declare @DateValue int, @DateDMY date;
set dateformat dmy;

set @DateDMY= '1/1/2014';
set @DateValue= day(@DateDMY) + (month(@DateDMY) * 256) + (year(@DateDMY) * 65536)
print @DateValue;
set @DateDMY= '1/2/2014';
set @DateValue= day(@DateDMY) + (month(@DateDMY) * 256) + (year(@DateDMY) * 65536)
print @DateValue;```

José Diz     Belo Horizonte, MG - Brasil

• Proposed as answer by Monday, April 14, 2014 7:21 AM
• Marked as answer by Monday, April 14, 2014 3:44 PM
• Edited by Monday, April 14, 2014 9:15 PM (day = number mod 2⁸)
Sunday, April 13, 2014 6:11 PM

### All replies

• How are you exporting to excel? If you are using query you can try like this,

select cast('01.01.2014' as datetime)

i.e select cast(datetimefield as datetime) from tablename

Regards, RSingh

Sunday, April 13, 2014 9:31 AM
• So do you want to save 01.01.2014 in EXCEL?

What is 131989761?

Declare @value int;
Set @value = 131989761;

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Sunday, April 13, 2014 9:34 AM
• Hi

Thank you very much for your help.

If I use this

select cast(datetimefield as datetime) from tablename

Orj.

SELECT cast(NUMFLDS2 as datetime)  FROM [LOGO_BORDROP].[dbo].[LH_001_DEFNFLDSCARDV]

Error is "Arithmetic overflow error converting expression to data type datetime."

The program saves dates like "131989761". The program shows me "01.01.2014" but in SQL in field date like this "131989761"

and

Declare @value int;
Set @value = 131989761;

This turns me "2014-07-17 02:42:41.000" but "131989761" must be "01.01.2014"

Berk

• Edited by Sunday, April 13, 2014 10:19 AM
Sunday, April 13, 2014 10:18 AM
• What is the datatype for NUMFLDS2 field ? Is it varchar ? It seems like the field value is not consistent.

Can you run the below command ? Post some results.

SELECT NUMFLDS2, IsDate(NUMFLDS2) IsDateOrNot FROM [LOGO_BORDROP].[dbo].[LH_001_DEFNFLDSCARDV]

WHERE IsDate(NUMFLDS2) = 0

Regards, RSingh

Sunday, April 13, 2014 10:49 AM
• You may need to talk to the vendor for the application. Or do your own reverse engineering. There is no apparent translation of the numbers.

You say that

01.01.2014 => 131989761
01.02.2014 => 131990017

You don't tell is what you mean with 01.02.2014. If I am to guess from your name, you mean Feb 1st 2014, but for some people it could also mean Jan 2nd.

The difference between the two numbers you have is 256. In a single day there is 86400 seconds, 864 minutes and 24 hours. If you intend this to be a month interval, it is even more difficult to make sense out of, as I would expect a factor of 31 to appear. That is, if the numbers are the number of seconds or minutes since some magic date.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Sunday, April 13, 2014 10:54 AM
• Why 131989761 must be 01.01.2014, based on what? You won't be able to convert this number in EXCEL as well to date.

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Sunday, April 13, 2014 10:54 AM
• May be

```-- code #1 v3declare @DateValue int, @DateDMY date;
set dateformat dmy;

--
set @DateValue= 131989761;
set @DateDMY= DateAdd(day, ((@DateValue-1) / 256 - 473947), 0);
SELECT @DateValue, @dateDMY;

--
set @DateValue= 131990017;
set @DateDMY= DateAdd(day, ((@DateValue-1) / 256 - 473947), 0);
SELECT @DateValue, @dateDMY;```

Code to generate numbers from date:
```-- code #2 v2declare @DateValue int, @DateDMY date;
set dateformat dmy;
set @DateDMY= '13/4/2014';
set @DateValue= ((DateDiff(day, 0, @DateDMY) + 473947) * 256) + 1;print @DateValue;```

Berk Ergun,
13/4/2014 => 132015873,
is this correct?

José Diz     Belo Horizonte, MG - Brasil

• Edited by Sunday, April 13, 2014 12:17 PM
Sunday, April 13, 2014 11:45 AM
• Solved.

Thank you very much every body.

I don't know what kind of logic it has but

every day it increases 1 unit

every mount it increases 256 unit

and for every year it increases 65536 unit.

So I took 01.01.1900 a base date and I formulate like this

\$B12 = 131991567 (for example)

124518657 = 01.01.1900 (The program found this)

=DATE(1900+INT((\$B12-124518657)/65536);INT((\$B12-(124518657+(INT((\$B12-124518657)/65536)*65536)))/256+1);(\$B12-((124518657+(INT((\$B12-124518657)/65536)*65536))+(256*((INT((\$B12-(124518657+(INT((\$B12-124518657)/65536)*65536)))/256+1))-1))))+1)

Result is "15.08.2014"

Sunday, April 13, 2014 2:40 PM
• every day it increases 1 unit
every mount it increases 256 unit
and for every year it increases 65536 unit.

8 bits for day; 8 bits for month.

date to number
number = (day * 2⁰) + (month * 2⁸) + (year * 2¹⁶)

number to date
day = number mod 2⁸;
month = number div 2⁸ mod 2⁸;
year = number div 2¹⁶;

`-- Excell / Calc=DATE( QUOTIENT(B12; 65536); MOD( QUOTIENT(B12; 256); 256); MOD(B12 ;256) )`

```-- code #1 v4  - number to date
declare @DateValue int, @DateDMY date;

--
set @DateValue= 131989761;
set @DateDMY= Convert(date, convert(varchar, (@DateValue % 256)) + '/' +
convert(varchar, (@DateValue / 256 % 256)) + '/' +
convert(varchar, (@DateValue / 65536)), 103)
SELECT @DateValue, @DateDMY;

--
set @DateValue= 131990017;
set @DateDMY= Convert(date, convert(varchar, (@DateValue % 256)) + '/' +
convert(varchar, (@DateValue / 256 % 256)) + '/' +
convert(varchar, (@DateValue / 65536)), 103)
SELECT @DateValue, @dateDMY;```

```-- code #2 v3  - date to number
declare @DateValue int, @DateDMY date;
set dateformat dmy;

set @DateDMY= '1/1/2014';
set @DateValue= day(@DateDMY) + (month(@DateDMY) * 256) + (year(@DateDMY) * 65536)
print @DateValue;
set @DateDMY= '1/2/2014';
set @DateValue= day(@DateDMY) + (month(@DateDMY) * 256) + (year(@DateDMY) * 65536)
print @DateValue;```

José Diz     Belo Horizonte, MG - Brasil

• Proposed as answer by Monday, April 14, 2014 7:21 AM
• Marked as answer by Monday, April 14, 2014 3:44 PM
• Edited by Monday, April 14, 2014 9:15 PM (day = number mod 2⁸)
Sunday, April 13, 2014 6:11 PM
• Solved.

Thank you very much every body.

I don't know what kind of logic it has but

every day it increases 1 unit

every mount it increases 256 unit

and for every year it increases 65536 unit.

So I took 01.01.1900 a base date and I formulate like this

\$B12 = 131991567 (for example)

124518657 = 01.01.1900 (The program found this)

=DATE(1900+INT((\$B12-124518657)/65536);INT((\$B12-(124518657+(INT((\$B12-124518657)/65536)*65536)))/256+1);(\$B12-((124518657+(INT((\$B12-124518657)/65536)*65536))+(256*((INT((\$B12-(124518657+(INT((\$B12-124518657)/65536)*65536)))/256+1))-1))))+1)

Result is "15.08.2014"

Don't you think this is not the proper way to store dates. Just imagine if there were calculated measures on such columns. How much time your ROLAP will take to process.

Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

Monday, April 14, 2014 10:29 AM
• Don't you think this is not the proper way to store dates. Just imagine if there were calculated measures on such columns. How much time your ROLAP will take to process.

Don't shoot the messenger. :-) As I understood it, Berek is working with an application, where someone has made this crude design.

But you are right, this is not how to store dates.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Monday, April 14, 2014 11:36 AM
• every day it increases 1 unit
every mount it increases 256 unit
and for every year it increases 65536 unit.

8 bits for day; 8 bits for month.

date to number
number = (day * 2⁰) + (month * 2⁸) + (year * 2¹⁶)

number to date
day = number mod 2⁰;
month = number div 2⁸ mod 2⁸;
year = number div 2¹⁶;

`-- Excell / Calc=DATE( QUOTIENT(B12; 65536); MOD( QUOTIENT(B12; 256); 256); MOD(B12 ;256) )`

```-- code #1 v4  - number to date
declare @DateValue int, @DateDMY date;

--
set @DateValue= 131989761;
set @DateDMY= Convert(date, convert(varchar, (@DateValue % 256)) + '/' +
convert(varchar, (@DateValue / 256 % 256)) + '/' +
convert(varchar, (@DateValue / 65536)), 103)
SELECT @DateValue, @DateDMY;

--
set @DateValue= 131990017;
set @DateDMY= Convert(date, convert(varchar, (@DateValue % 256)) + '/' +
convert(varchar, (@DateValue / 256 % 256)) + '/' +
convert(varchar, (@DateValue / 65536)), 103)
SELECT @DateValue, @dateDMY;```

```-- code #2 v3  - date to number
declare @DateValue int, @DateDMY date;
set dateformat dmy;

set @DateDMY= '1/1/2014';
set @DateValue= day(@DateDMY) + (month(@DateDMY) * 256) + (year(@DateDMY) * 65536)
print @DateValue;
set @DateDMY= '1/2/2014';
set @DateValue= day(@DateDMY) + (month(@DateDMY) * 256) + (year(@DateDMY) * 65536)
print @DateValue;```

José Diz     Belo Horizonte, MG - Brasil

Excellent results.

All of them worked perfectly. I used your SQL code in my report.

Thank you.

Berk

Monday, April 14, 2014 3:59 PM
• Don't you think this is not the proper way to store dates. Just imagine if there were calculated measures on such columns. How much time your ROLAP will take to process.

Don't shoot the messenger. :-) As I understood it, Berek is working with an application, where someone has made this crude design.

But you are right, this is not how to store dates.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Thank you Erland, I am the victim, not killer :)

Berk

Monday, April 14, 2014 4:02 PM