January 31, 2019 at 12:52 am
Hello,
I have a stored procedure that takes date as input parameter but it converts that date parameter to a float data type. Is it common practice to do so, does it influence query efficiency in such a way that it is worth to do so?
Below code:(floor(convert(float, GetDate())))
January 31, 2019 at 2:11 am
Is it common practice to convert a date(time) datatype to a float? No, not really. Does it improve efficiency? That's a carrot on a stick question; if you end up comparing a float column to a datetime column then no, it'll ruin performance.
Why do you want to store a date(time) as a float? There's very rarely a good "excuse" for not using an appropriate datatype for your data, and you have datetime, datetime2, date, smalldatetime, datetimeoffset and time to choose from for date(time) data; what's wrong with those?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2019 at 2:26 am
Tom, thank you very much for replying.
It is my mistake that i haven't mentioned in first post that this conversion takes place in the WHERE clause, so actual data is stored as a date type and it is converted to float only in the filter expression. It's a solution that i need to work with but i haven't built it on my own.
January 31, 2019 at 2:34 am
lukaszpiech - Thursday, January 31, 2019 2:26 AMTom, thank you very much for replying.
It is my mistake that i haven't mentioned in first post that this conversion takes place in the WHERE clause, so actual data is stored as a date type and it is converted to float only in the filter expression. It's a solution that i need to work with but i haven't built it on my own.
A conversion in a WHERE doesn't always make it non-SARGable; it's apply the function to a column (normally) that is the problem. For example, both the causes below are SARGable:WHERE YourFloatDateColumn = FLOOR(CONVERT(float, GETDATE()))
WHERE YourFloatDateColumn = FLOOR(CONVERT(float, @SomeDate)))
On the other hand something like this isn't SARGable:WHERE CONVERT(float, YouDateColumn) = @SomeFloat
FROM Table1 T1
Table2 T2 ON CONVERT(float,T1.SomeDate) = T2.SomeFloat
FROM Table1 T1
Table2 T2 ON CONVERT(date,T1.SomeFloat) = T2.SomeDate
But, again, why are you storing date(time)s as a float? That is (probably) only going to bite you in the foot.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2019 at 2:53 am
But, again, why are you storing date(time)s as a float? That is (probably) only going to bite you in the foot.
The OP isn't storing the date as a float, the CONVERT converts a date to a float. I've seen it in some of our older stored procs and it's a workaround for getting the date from a datetime in older editions of SQL Server. It's not exactly common practice but it's definitely not unheard of.
The portion of the float to the left of the decimal point represents the date and the decimal is the time part. If you converted the date to a float you could manipulate it more easily without the date and time functions that were only introduced in 2008 (I think).
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 31, 2019 at 8:20 am
Does it store the result back into a datetime variable? If so, using float to strip the time is not a problem.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply