Search code examples
sqlsql-servert-sqlrow-number

Not able to set row number by ordered by datetime

I currently try to give my rows in the following table an index

    ticket_id(bigint)       val(nvarchar(4000)) 
    99856                   15.01.2019 16:58    
    99856                   15.01.2019 17:20    
    99921                   15.01.2019 17:31    
    100197                  16.01.2019 09:55

The index should've been generated by the following logic

    row_number() over (Partition by ticket_id Order by convert(datetime,val,104) asc) as nr

However, this query returns

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

It works when I use the following query 

    row_number() over (Partition by cast(ticket_id as int) Order by convert(datetime,val,104) asc) as nr

Can someone explain why?

Solution

  • Not correct conversion, try find bad values

    SELECT * FROM [youTable] WHERE TRY_CONVERT(datetime,val,104) IS NULL

    upd

    when I apply "Try_convert", I do not get any Error

    TRY_CONVERT

    Return Types
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

    for example 

    SELECT TRY_CONVERT(datetime,'99.99.9999',104)