How SQL's Convert Function Work When Converting Datetime to Float

How SQL's convert function work when converting datetime to float?

DateTime is often represented as a day count from a pre-determined date (generally know as the epoch) on the integer part and the percentage of the day elapsed since mid-night on the fractional part.

SQL Server is not the exception to this, thus the conversion to Float makes a lot of sense. Day 0 is Jan 01 1900 00:00:00 (AFAIK, in no particular time-zone, so you shall consider it "local time").

So, you can try this:

declare @ADate DateTime;
set @ADate = '19000101 00:00:00';
select CONVERT(float, @ADate);  --should print 0
set @ADate = '19000101 12:00:00';
select CONVERT(float, @ADate);  --should print 0.5
set @ADate = '19001231 06:00:00';
select CONVERT(float, @ADate);  --should print 364.25

So, for your results, 40183 days has been passed since 01/01/1900 00:00:00 and 01/07/2010 00:00:00

Clarification: Unix like systems use a different approach to store datetimes: Seconds since Unix epoch (Jan 1 1970 00:00:00 UTC), which is more known as epoch time.

[Edit]
Date format on this response was changed to YYYYMMDD format on 20140416, after some new years of experience with SQL Server (and as @Damien said in his comment) this is the only safe format.

Convert datetime to float with specified format in T-SQL

I don't know how better this is, but it's shorter:

SELECT CAST(DATEDIFF(s, @d, DATEFROMPARTS(YEAR(@d), 1, 1)) as FLOAT) / 
  DATEDIFF(s, DATEFROMPARTS(YEAR(@d) + 1, 1, 1), DATEFROMPARTS(YEAR(@d), 1, 1)) + 
  YEAR(@d)

(SQLFiddle)

Datetime colum to float type conversion is getting issue in sql server

Without knowing your desired output, you could just: Add Float column, populate, drop date column.

To my knowledge you cannot add a CONVERT to an ALTER statement, anyone know otherwise?

Converting a FLOAT into DATE in SQL?

If your value is YYYYMM, then one simple method is to convert to a string and then a date:

select convert(date, convert(varchar(255), yyyymm) + '01')

Or, use datefromparts():

select datefromparts(floor(yyyymm / 100), yyyymm % 100, 1)

Convert time float to format HH:mm sql server

Use function FORMAT SQL 2012+
https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

DECLARE  @input float = 4.92    
SELECT FORMAT(FLOOR(@input)*100 + (@input-FLOOR(@input))*60,'00:00')

---

Related Topics