[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 764
  • Last Modified:

t-sql date field with null value


I have a date field (test_date) that have null value in it. I am using 2008 R2 server.
the following query return value '1900-01-01' when the test_date field has null value


      select
       case
         WHEN isdate(TEST_DATE) = 1 then convert(date, test_date, 120)
         else ''
       end
       from test_tb
  I am not sure whey ???
0
jfreeman2010
Asked:
jfreeman2010
  • 5
  • 5
  • 2
  • +1
2 Solutions
 
Ephraim WangoyaCommented:


select
       case
         WHEN TEST_DATE IS NULL then
            ''
        else
            convert(varchar(10), test_date, 120)
       end
from test_tb
0
 
sventhanCommented:
This is not an issue. When you cast a zero length string or a null value  to datatype of datetime, it casts it to the SQL Server zero date, 1900-01-01 00:00:00.000
0
 
jfreeman2010Author Commented:
ewangoya,
 I try your suggest, still see null value show '1900-01-01' in the result.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sventhanCommented:
I ran your code and get the 1900-01-01 result

declare
@tdate datetime
set @tdate = Null

select
       case
         WHEN isdate(@tdate) = 1 then convert(date, @tdate, 120)
         else ''
       end
0
 
jfreeman2010Author Commented:
sventhan,

how to handle this so my result query will have value '' NOT '1900-01-01' in the result dataset?
0
 
sventhanCommented:
Its a expected behavior. What do you like to do?
0
 
sarabhaiCommented:
TRY this

select
       STUFF(CONVERT(char(19),   TEST_DATE , 100), 1, 12, '')
      from test_tb
0
 
sventhanCommented:
You got the answer already from ewangoya. I just repeat the code for you...

select
       case
         WHEN TEST_DATE IS NULL then
            'do whatever you wanted to do'
        else
            convert(varchar(10), test_date, 120)
       end
from test_tb
0
 
jfreeman2010Author Commented:
sarabhai,

your select give me null value in the result.

sventhan, your case still result value '1900-01-01'.  I am try to get the value of ''.  

Please help.  Thank you.
0
 
sventhanCommented:
declare
@tdate datetime
set @tdate = Null

select
       case
         WHEN @tdate IS NULL then
            ''
        else
            convert(varchar(10), @tdate, 120)
       end
0
 
jfreeman2010Author Commented:
THIS WORKS:

declare
@tdate datetime
set @tdate = Null

 select case WHEN @tdate IS NULL then ''
         else convert(varchar(10), @tdate, 120)
       end

BUT when I try the same in my working query, still show the '1900-01-01' value.....
0
 
jfreeman2010Author Commented:
I see my problem...

when I convert, I have it like this:

convert(date, @tdate, 120), this didn't work in this case. when I changes to varchar(10), it works now.

THANK YOU!!!
0
 
Ephraim WangoyaCommented:

try checking for both conditions then

select
       case
         WHEN (TEST_DATE IS NULL) or (TEST_DATE  <= '19000101') then
            ''
        else
            convert(varchar(10), test_date, 120)
       end
from test_tb
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now