Convert Date to Float 1

[Halliarse]

Hi

I have an old database that stores dates as floats and I need to change 2 records on one of the tables and the front end generates an error. As it is only 2 records, I have suggested, to save time at least, that I manually amend the SQL record!

Unfortunately I do not know how to convert the date to the float!

I have an example from the database that has 3599164800 stored, which displays a date of 2018-01-19 00:00:00

I want the date to display 2018-02-05 00:00:00, how do I get the 'float' value please?

Thanks

Steve

 

[Olaf Doschke]

With one example only it's not safe to make any assumptions. As I understand it If you have trouble getting the frontend to run, but it shouldn't be hard to find aplace in the code that float number is turned to a datetime for display.

One integer based datetime is unix time with the number meaning seconds since 1970-01-01 0am UTC/GMT. What speaks against this is the float nature, though the value you show is an integer. Another possible way to get from a float to a dateime is using DATEADD, some of the most famous ways to determine the datetime without time portion (before the date type was introduced) via [tt]SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), [highlight #FCE94F]0[/highlight])[/tt] where the last 0 is a date in the form of days since 1900-01-01.

1970-01-01 + 3599164800 seconds is 2084 something and DATEADD does not work out with such high numbers, even taken as milliseconds.

Finally, as MSSQL has several datetime types the question is, why none of them was choosen.

You should look into your code, everything else is just guesswork.

Bye, Olaf.

 

[Olaf Doschke]

SELECT DATEADD(dd,-3599164800/60/60/24,'20180119')

This results in 1904-01-01 00:00:00.0 and thus hints on that date as reference and the float meaning seconds - convertable to days by dividing via /60 (minutes) /60 (hours) /24 (days)-
Still, you should find something like that either in queries done on the data or on the client side. Search 1904 in the code and you'd likely find the conversion used, if that assumption is true.

Bye, Olaf.

 

[Halliarse]

Hi Olaf

Unfortunately, the reason for the field being declared as a float is beyond me as this is an old database! However, whilst your response didn't resolve my issue, you certainly pointed me in the right direction and I have sorted my issue, so I thank you for that!

Best wishes

Steve

 

[Andrzejek]

Would you be so kind and share your solution?
Others searching this thread may benefit.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

I am also curious. A single example doesn't determine the proper conversion, but it seems plausible what you need is number of seconds since 1904-01-01 00:00:00.0 to store for correcting the 2 records you initially spoke of. It is straight forward this results from SELECT DATEDIFF(seconds,'19040101',@somedate) with @somedate being some date you want to "convert" to float. This just has the problem of DATEDIFF only generating differences within 32bit integer value range and for current dates that range is insufficient you get an overflow error.. To overcome that you would rather compute days since 1904-01-01 and multiply by 24*60*60.

For example

SELECT DATEDIFF(dd,'19040101','20171011')*24.0*60.0*60.0

results in 3590524800 and that would be the float number corresponding to today (11th October 2017) 0 am, if the assumptions about the root date and meaning of the float are correct. You could be more helpful here, Steve, by providing more examples and sharing what you finally did.

I know you are just IT/Management and no developer, but we just were3 at the step of you confirming that somehow, via the found root date, for example. Giving more examples, whatever. If you were unable to deduct you now need DATEDIFF or failed on that value range, you're always free to ask further. We usually don't answer all necessary steps covering all eventualities and branches irrelevant, if a certain step fails anyway. So I expected you to come back with further questions.

It's fine you made it, but it could have been less cumbersome, I think, if you would have continued the conversation.

I didn't blame you for the situation, I merely pointed out the fact this is unfortunate and asked, why none of the usual datetime types was used. We can't tell, this is not your design or your companies design, so this wasn't any accusation in the first place, merely a question. I also didn't expect the answer directly from you, but you should have contact to the developers of this or the vendor of the product, shouldn't you?
I don't know, maybe there is a simple reason for that, the choice of doing so, as a company was founded 1904 and this way of storing dates is its tribute to that fact. A bad idea to go off standards to use such a root, but that root might also come from any legacy software used. What matters is, the reasoning will reveal such a reference date to ensure the conversion correctness. You can't prove that from a single reference examples, especially as it turns out not even to adhere to the usual Unix Timestamp standard.

Bye, Olaf.