INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Convert Date to Float

Convert Date to Float

(OP)
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

RE: Convert Date to Float

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 SELECT DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) 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.

RE: Convert Date to Float

CODE

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.

RE: Convert Date to Float

(OP)
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

RE: Convert Date to Float

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.

RE: Convert Date to Float

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close