none
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.

    Please somebody help me :)

    Berk

    Sunday, April 13, 2014 9:27 AM

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⁸)
    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;
    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


    Sunday, April 13, 2014 9:34 AM
    Answerer
  • 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 "2014-07-17 02:42:41.000" but "131989761" must be "01.01.2014"

    Berk


    • Edited by BerkErgun 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
    Answerer
  • May be

    -- code #1 v3
    declare @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 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
    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 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⁸)
    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