Answered by:
9 digit date number (ex.01.01.2014 => 131989761)
Question
Answers

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 Ibrahim Basha Shaik Monday, April 14, 2014 7:21 AM
 Marked as answer by BerkErgun Monday, April 14, 2014 3:44 PM
 Edited by Jose.Diz Monday, April 14, 2014 9:15 PM (day = number mod 2⁸)
All replies


So do you want to save 01.01.2014 in EXCEL?
What is 131989761?
Declare @value int;
Set @value = 131989761;
Select DateAdd(ss, @value % 10000, DateAdd(dd, (@value / 10000) % 1000,
DateAdd(yy, @value / 10000000, '20001231')));
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
 Edited by Uri DimantMVP, Editor Sunday, April 13, 2014 9:35 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;
Select DateAdd(ss, @value % 10000, DateAdd(dd, (@value / 10000) % 1000,
DateAdd(yy, @value / 10000000, '20001231')));This turns me "20140717 02:42:41.000" but "131989761" must be "01.01.2014"
Berk
 Edited by BerkErgun Sunday, April 13, 2014 10:19 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
 Edited by RSingh() Sunday, April 13, 2014 10:54 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 => 131990017You 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 
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

May be
 code #1 v3
declare @DateValue int, @DateDMY date; set dateformat dmy;  set @DateValue= 131989761; set @DateDMY= DateAdd(day, ((@DateValue1) / 256  473947), 0); SELECT @DateValue, @dateDMY;  set @DateValue= 131990017; set @DateDMY= DateAdd(day, ((@DateValue1) / 256  473947), 0); SELECT @DateValue, @dateDMY;
Code to generate numbers from date:
 code #2 v2
declare @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 Jose.Diz Sunday, April 13, 2014 12:17 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(($B12124518657)/65536);INT(($B12(124518657+(INT(($B12124518657)/65536)*65536)))/256+1);($B12((124518657+(INT(($B12124518657)/65536)*65536))+(256*((INT(($B12(124518657+(INT(($B12124518657)/65536)*65536)))/256+1))1))))+1)
Result is "15.08.2014"

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 Ibrahim Basha Shaik Monday, April 14, 2014 7:21 AM
 Marked as answer by BerkErgun Monday, April 14, 2014 3:44 PM
 Edited by Jose.Diz Monday, April 14, 2014 9:15 PM (day = number mod 2⁸)

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(($B12124518657)/65536);INT(($B12(124518657+(INT(($B12124518657)/65536)*65536)))/256+1);($B12((124518657+(INT(($B12124518657)/65536)*65536))+(256*((INT(($B12(124518657+(INT(($B12124518657)/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.
 Proposed as answer by Naomi NModerator Monday, April 14, 2014 10:20 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 
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

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.seThank you Erland, I am the victim, not killer :)
Berk