1

I'm working with an application that ( for whatever reason... ) is unable to pass datetime stamps into its SQL Server database. Where the application needs a datetime stamp, it will push the server datetime in string format into a string datapoint. For many situations, it is perfectly adequate to convert via case isdate(ApplicationDatetimestamp) when 1 then convert( datetime, ApplicationDatetimestamp ) end

However:

This is complicated by the fact that many (far too many...) users have been given admin access in the application and are permitted to bypass the standard input methods. Sometimes this is a very bad problem ( expect '2015-11-17 20:32:09.340', get 'Tuesdya at ten thirty' ), sometimes it's a marginally less bad problem, ( expect '2015-11-17 20:32:09.340', get '11/17/2015' ).

I've been tasked with calculating KPI's from fields of this type. I've largely given up on those fields where implicit conversion does not work ( very bad problem examples ), but I'm having some difficulty discarding instances where implicit conversion succeeds but is incorrect due to scale truncation ( the less bad problem issue ).

Example

I've been asked to show the time difference from input to a due date ( open of business the next day ). I take the user input and calculate datediff() to the sanitized input, right? Wrong

declare
    @GoodInput varchar( 100 ) = '2015-11-17 20:32:09.340',
    @LessBadInput varchar( 100 ) = '11/17/2015',
    @VeryBadInput varchar( 100 ) = 'Tuesdya at ten thirty';

declare 
    @DueBy datetime = '2015-11-18 08:31:00.00',
    @GoodDatetime datetime = convert( datetime,@GoodInput ),
    @LessbadDatetime datetime = case isdate( @LessBadInput ) when 1 then convert( datetime, @LessBadInput ) end,
    @VeryBadDatetime datetime = case isdate( @VeryBadInput ) when 1 then convert( datetime, @VeryBadInput ) end;

select
    GoodInput =     datediff( hour, @GoodDatetime, @DueBy ),
    LessBadInput =  datediff( hour, @LessBadDatetime, @DueBy ),
    VeryBadInput =  datediff( hour, @VeryBadDatetime, @DueBy );

returns...

+-----------+--------------+--------------+
| GoodInput | LessBadInput | VeryBadInput |
+-----------+--------------+--------------+
|        12 |           32 | NULL         |
+-----------+--------------+--------------+

For the purposes of calculating a business metric, it's better to have no input than partial input... The user who put in the partial date has now tripled the calculated metric - arguably a much worse result than that of the user who put in the misspelled Tuesdya.

Question:

Is there a preferred method to calculate implicit scale of SQL Server datetime scalar?

If I were able to - in an interim step - determine that the row contains no specificity past the DAY level, I could exclude the row from calculation.

The methods I've explored thus far are:

1: the obvious string manipulation

Using the below method

with StrParse ( Id, Name, Value, StrValue )  as
(
    select 1, 'Good', @GoodDatetime, convert( varchar, @GoodDatetime, 126 )
    union select 2, 'LessBad', @LessBadDatetime, convert( varchar, @LessBadDatetime, 126 )
    union select 3, 'VeryBad', @VeryBadDatetime, convert( varchar, @VeryBadDatetime, 126 )
)
select
    Id,
    rStr =  reverse( StrValue ),
    fNum =  patindex( '%[1-9]%', reverse( StrValue ) ), -- find 
    Value
from StrParse

we return

+----+-------------------------+------+-------------------------+
| Id |          rStr           | fNum |          Value          |
+----+-------------------------+------+-------------------------+
|  1 | 043.90:23:02T71-11-5102 | 2    | 2015-11-17 20:32:09.340 |
|  2 | 00:00:00T71-11-5102     | 10   | 2015-11-17 00:00:00.000 |
|  3 | NULL                    | NULL | NULL                    |
+----+-------------------------+------+-------------------------+

Using this, we'd simply discard anything with an fNum < 8 when we want "hours" scale as the minimum allowed input exactness.

However, the string manipulation method ( as currently listed ) seems inelegant. The implicit datetime conversion to string truncates millisecond scale when not present, which provides a different fNum scale interpretation ( fNum of 10 for Id=2 is the same precision as an fNum of 14 for Id=1 ). Obviously, this could be corrected with more string manipulation, but then I'm running down a rabbit hole I never meant to when I just wanted to determine the scale of a datetime scalar...

043.90:23:02T71-11-5102
+-------+-------+--------------------------+-----------------+
| digit | place |          Scale           | SecondPartPower |
+-------+-------+--------------------------+-----------------+
| 0     |     1 | millisecond              | -3              |
| 4     |     2 | millisecond ( tens )     | -2              |
| 3     |     3 | millisecond ( hundreds ) | -1              |
| .     |     4 | "."                      | --              |
| 9     |     5 | Seconds ( ones )         | 0               |
| 0     |     6 | Seconds ( tens )         | 1               |
| :     |     7 | ":"                      | --              |
| 2     |     8 | Minutes ( ones )         | 10^1 * 6        |
| 3     |     9 | Minutes ( tens )         | 10^2 * 6        |
| :     |    10 | ":"                      | --              |
| 0     |    11 | Hours ( ones )           | 10^1 * 360      |
| 2     |    12 | Hours ( tens )           | 10^2 * 360      |
| T     |    13 | "T"                      | --              |
| 7     |    14 | day                      |                 |
+-------+-------+--------------------------+-----------------+

2: Conversion to numeric

The immediate concerns become apparent with the below query

declare
    @Today date = getdate(),
    @TodayDt datetime = getdate();
declare
    @TenOclock datetime = convert( datetime, @Today ) + convert( time, '10:00:00.0' ),
    @TenOhOne datetime = convert( datetime, @Today ) + convert( time, '10:01:00.0' );

select 
    Today =         @Today,
    TodayLong =     convert( datetime, @Today ),
    TodayDt =       @TodayDt,
    TenOclock =     @TenOclock,
    TenOhOne =      @TenOhOne;

select 
    --Today =       convert( float, @Today ), -- Explicit conversion from data type date to float is not allowed.
    TodayLong =     convert( float, convert( datetime, @Today ) ),
    TodayDt =       convert( float, @TodayDt ),
    TenOclock =     convert( float, @TenOclock ),
    TenOhOne =      convert( float, @TenOhOne );

returns

+-----------+------------------+------------------+------------------+
| TodayLong |     TodayDt      |    TenOclock     |     TenOhOne     |
+-----------+------------------+------------------+------------------+
|     42323 | 42323.9012709877 | 42323.4166666667 | 42323.4173611111 |
+-----------+------------------+------------------+------------------+

Our nice and even @TenOclock becomes the rounding mess 42323.4166666667 instead of a nice, decimal place stop. I'm not sure how I would even begin to interpret the rounded fractions without the ( once again ) string manipulation.

So... is there a more elegant method?

Obviously, when this is needed, it's nice to convert to a UDF. but I'd really like to have a METHOD that works before trying to compartmentalize and standardize instances where I need this executed.

migrated from stackoverflow.com Nov 25 '15 at 14:51

This question came from our site for professional and enthusiast programmers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.