CODEWITHSUNDEEP
sqlsql-serverdatetime
Nilesh Gajare
Nilesh Gajare

Reputation: 6398

Compare Datetime values on nvarchar columns in SQL

In SQL Azure server database, a table has a nvarchar column that contains date values in string format (dd/MM/yyyy). e.g. 03/11/2011

Column ARTICLE1 contains null values for some records.

Below is the query I have tried so far but I'm getting an error

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

SQL query

select 
    [Code], concat(fname,' ',lname) as Name, [ARTICLE1] 
from 
    #T2 
where    
    CAST(ISNULL( [ARTICLE1], '1900-01-01') AS DATETIME) >= CONVERT(DATETIME, '31/01/1920', 103) 
    and CAST(ISNULL( [ARTICLE1] , '1900-01-01') AS DATETIME) <= CONVERT(DATETIME, '31/01/2017', 103) 
    and Category in ('STAFF', 'MANAGER')

Upvotes: 0

Views: 1644

Answers (1)

Nilesh Gajare
Nilesh Gajare

Reputation: 6398

I got the solution, Need to convert the string to datetime instead of CAST

select [Code],concat(fname,' ',lname) as Name,[ARTICLE1] From #T2 where    
 CONVERT( DATETIME, ISNULL( [ARTICLE1] , '1900-01-01'), 103 )  >= CONVERT( DATETIME, '31/01/1920', 103 ) and 
 CONVERT( DATETIME, ISNULL( [ARTICLE1] , '1900-01-01'), 103 )  <= CONVERT( DATETIME, '31/01/2017' , 103 ) and Category in ('STAFF','MANAGER')

Upvotes: 2

Related Questions