locked
Conversion of float value to datetime RRS feed

  • Question

  • Hi,

    I am working with an existing application that hosts data on an MS SQL Server Express 2008.

    All dates and times are stored in the tables with the int datatype and all datetimes with the float datatype. 

    I have difficulty in converting those values to dates, times and datetimes. When I used the CAST or CONVERT functions it gives me dates of 3081. 

    See below some sample data. Any ideas?

    PKOID BeginDateOID BeginTimeOID EndDateOID EndTimeOID ShotCount TotalQty StartDateTime EndDateTime
    1 735477 56339453 735477 56372665 0 0 63545269139.45 63545269172.67
    2 735477 56373710 735477 56388842 0 0 63545269173.71 63545269188.84
    3 735477 56400230 735477 57240842 17 17 63545269200.23 63545270040.84
    4 735477 57131547 735477 57265287 0 0 63545269931.55 63545270065.29
    5 735477 57344131 735477 57372946 0 0 63545270144.13 63545270172.95
    6 735477 57555148 735477 57563463 0 0 63545270355.15 63545270363.46
    7 735477 57564493 735477 57584946 0 0 63545270364.49 63545270384.95
    8 735477 58593901 735477 58720968 0 0 63545271393.90 63545271520.97
    9 735477 59392777 735477 59399922 0 0 63545272192.78 63545272199.92
    10 735477 59400952 735477 61199960 0 0 63545272200.95 63545273999.96

    Marios

    Saturday, February 17, 2018 11:44 AM

All replies

  • I don't think you can convert float to datetime datatype directly. You will first have to convert float to varchar, and then varchar to datetime.

    Converting float to datetime

    Or, try something like this:

    select cast(cast(StartDateTime as varchar(255)) + '0101' as datetime)

    I would recommend stop storing dates as float and start storing them as datetime going forward. That way you don't have to worry about messy datatype conversions in the future.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Saturday, February 17, 2018 1:12 PM
    Saturday, February 17, 2018 1:08 PM
  • It seems the integers are stored as serial numbers from a base date/time value. It would be easier if you were using SQL Server 2012 so that you could use DATETIMEFROMPARTS but you can instead use DATEADD/DATEDIFF in SQL 2008.

    Below is an example, assuming 1) times are number of milliseconds from midnight, 2) dates are number of dates since '0001-01-01', and 3) datetimes are number of seconds since '0001-01-01T00:00:00'. With these assumptions, I get values for date '2014-09-02' using the sample data provided. 

    DECLARE @sample AS TABLE(
    	  PKOID int 
    	, BeginDateOID int
    	, BeginTimeOID int
    	, EndDateOID int
    	, EndTimeOID int
    	, ShotCount int
    	, TotalQty int
    	, StartDateTime float
    	, EndDateTime float
    );
    INSERT INTO @sample VALUES
    	  (1, 735477, 56339453, 735477, 56372665, 0, 0, 63545269139.45, 63545269172.67)
    	, (2, 735477, 56373710, 735477, 56388842, 0, 0, 63545269173.71, 63545269188.84)
    	, (3, 735477, 56400230, 735477, 57240842, 17, 17, 63545269200.23, 63545270040.84)
    	, (4, 735477, 57131547, 735477, 57265287, 0, 0, 63545269931.55, 63545270065.29)
    	, (5, 735477, 57344131, 735477, 57372946, 0, 0, 63545270144.13, 63545270172.95)
    	, (6, 735477, 57555148, 735477, 57563463, 0, 0, 63545270355.15, 63545270363.46)
    	, (7, 735477, 57564493, 735477, 57584946, 0, 0, 63545270364.49, 63545270384.95)
    	, (8, 735477, 58593901, 735477, 58720968, 0, 0, 63545271393.90, 63545271520.97)
    	, (9, 735477, 59392777, 735477, 59399922, 0, 0, 63545272192.78, 63545272199.92)
    	, (10, 735477, 59400952, 735477, 61199960, 0, 0, 63545272200.95, 63545273999.96);
    
    SELECT 
    	  PKOID
    	, DATEADD(day, BeginDateOID, CAST('0001-01-01' AS date)) AS BeginDateOID
    	, DATEADD(millisecond, BeginTimeOID, CAST('' AS time)) AS BeginTimeOID
    	, DATEADD(millisecond, BeginTimeOID, DATEADD(day, BeginDateOID, CAST('0001-01-01' AS datetime2))) AS BeginDateTimeOID
    	, DATEADD(day, EndDateOID, CAST('0001-01-01' AS date)) AS EndDateOID
    	, DATEADD(millisecond, EndTimeOID, CAST('' AS time)) AS EndTimeOID
    	, DATEADD(millisecond, EndTimeOID, DATEADD(day, EndDateOID, CAST('0001-01-01' AS datetime2))) AS EndDateTimeOID
    	, ShotCount
    	, TotalQty
    	, DATEADD(millisecond, (StartDateTime - (CAST(StartDateTime / 60 AS int) * 60.0)) * 1000, DATEADD(minute, CAST(StartDateTime / 60 AS int), CAST('0001-01-01' AS datetime2))) AS StartDateTime
    	, DATEADD(millisecond, (EndDateTime - (CAST(EndDateTime / 60 AS int) * 60.0)) * 1000, DATEADD(minute, CAST(EndDateTime / 60 AS int), CAST('0001-01-01' AS datetime2))) AS EndDateTime
    FROM @sample;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, February 17, 2018 2:21 PM