CODEWITHSUNDEEP
sql-server-2008t-sqldate
vicangel
vicangel

Reputation: 162

T-SQL: How to cast successfully a Null value to date type?

In my example, I have a table with 1 column date and i insert 3 values 1 of them is null. As you can see in the first select the null value goes first after order by. In the 2 select, although i cast a string as date the previously null value after order by remains first . Here is my query you will understand better the problem if you run it! I have also tried convert

drop table table1

create table table1(
dates date)

insert into table1 values ('2017-9-1'),('2017-7-1'),(NULL)

select dates from table1 ORDER BY dates ASC

select ISNULL(dates,CAST('2020-04-25T15:50:59.997' AS date))from table1 ORDER BY dates ASC

select ISNULL(dates, '2017-8-1')from table1 ORDER BY dates ASC

Upvotes: 0

Views: 728

Answers (1)

Ian Kenney
Ian Kenney

Reputation: 6426

In all your select statements you are ordering by date field - perhaps you wanted to order by the expression?

for example: 

select dates from table1 ORDER BY dates ASC

select dates from table1 ORDER BY ISNULL(dates,CAST('2020-04-25T15:50:59.997' AS date)) ASC

select dates from table1 ORDER BY ISNULL(dates, '2017-8-1') ASC

or 

select dates from table1 ORDER BY dates ASC

select ISNULL(dates,CAST('2020-04-25T15:50:59.997' AS date))from table1 
ORDER BY ISNULL(dates,CAST('2020-04-25T15:50:59.997' AS date)) ASC

select ISNULL(dates, '2017-8-1')from table1 
ORDER BY ISNULL(dates, '2017-8-1') ASC

or you can use a sub query 

SELECT dates from ( 
  SELECT 
    ISNULL(dates,CAST('2020-04-25T15:50:59.997' AS date)) dates 
  FROM table1
) qry 
ORDER BY dates

Upvotes: 1

Related Questions