0
SET @secret= - (convert(float,getdate()) - convert(int,getdate()) ) * 1000000 ;

WHen I put the '-' before the (convert, it works after 12 AM but when I delete the '-' it works before 12 AM. This is seriously strange. What can I do to prevent this?

Damini Suthar
  • 1,470
  • 2
  • 14
  • 43
Faruk
  • 1
  • 2
  • 3
    What are you trying to accomplish with this code? – Gordon Linoff Nov 29 '17 at 13:05
  • If you want calculate time difference check this question https://stackoverflow.com/questions/26991807/calculate-time-difference-in-minutes-in-sql-server – Juan Carlos Oropeza Nov 29 '17 at 13:06
  • 1
    When you say "it works" or doesn't, what do you mean? What is this trying to do? – DavidG Nov 29 '17 at 13:07
  • Are you sure you mean before 12 am? – whodini9 Nov 29 '17 at 13:09
  • It's hard to explain in English but I'll give it a go. What I'm trying is to make a table with 9 columns. The information which goes in there are submitted by other people from an another application. The submitted infomration w ill go in to my table. Everything works good so far except for 1 thing. The table doesn't show on my webpage after 12 am because of the '-' before the convert. ANd I have seriously no idea how to solve this.. – Faruk Nov 29 '17 at 13:19
  • What you need to explain is what value you expect to be on `@secret` You are probably asking the wrong question [**What is the XY problem?**](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – Juan Carlos Oropeza Nov 29 '17 at 13:28

1 Answers1

0

You seem to want this calculation:

select -1000000 * datediff(second, 0, cast(getdate() as time)) / (24*60*60.0)

I would suggest that you use this instead of your more arcane approach to convert fractions of a day to a large number.

Or, if you prefer more precision:

select -1000000 * datediff(millisecond, 0, cast(getdate() as time)) / (24*60*60*1000.0)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786