none
30hr Datestamp stored as string

    Question

  • Hello

    We (for reasons not worth getting into) have all our data stored in a 30hr clock that 'starts' at 06:00 and ends at 30:00 (or rather 29:59, which would be 05:59 obviously). The main developers here always seem to busy to give my table view the integer type stored time, which is in seconds, thus easily converted - so I wanted to see if in my query I could just throw it a few functions in the SELECT statement to return a proper timestamp or double-point value for use in Excel/Access later on?

    I tried variations of "SELECT TO_TIMESTAMP(MOD(START_TIME,2400),'HH:MI:SS') FROM..." but to no avail. I just seem to be confusing it.

    Anyone know a way around this?

    Thanks

    C

    Wednesday, January 29, 2014 5:55 PM

All replies

  • DECLARE @wtf VARCHAR(255) = '29:59';
    
    SELECT	TIMEFROMPARTS( 
    			CAST(LEFT(@wtf, CHARINDEX(':', @wtf) - 1) AS INT) - 6,
    			CAST(RIGHT(@wtf, CHARINDEX(':', @wtf) - 1) AS INT),
    			0, 0, 0	);
    
    

    Wednesday, January 29, 2014 6:33 PM
  • My bad - forgot to mention this is for Oracle SQL, not SQL Server

    Thanks for the speedy reply though!

    Friday, January 31, 2014 2:58 PM
  • Then it's the wrong forum.. https://community.oracle.com/

    btw, the Oracle SQL is PL/SQL and our's here is T-SQL (Transact SQL)

    Friday, January 31, 2014 3:38 PM
  • Apologies and many thanks
    Friday, January 31, 2014 3:51 PM