Report returning 01/01/1900 in field when date value is null

Posted on 2007-10-16
Last Modified: 2012-06-21
I have a query that provides data for a report one of the fields is a date type and will on occasion be empty, however when that is the case sql server returns the value '01/01/1900' I want the field to display nothing if there is no value
I tried  =iif(me.Value = 01/01/1900, " ","me.Value") without success
in the query I tried   ISNULL(sub.Date_Returned,'')as Date_Returned also without succes
Question by:deNZity
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    empty and NULL are not the same thing, especially not for date type...
    now, with IIF() in the query, I assume you run the query on a linked table?
    can you clarify?


    Author Comment

    hi thanks for reply.

    The iif() is in the report, We use reporting services so in the textbox that displays the date I created an
    expression =iif(me.Value = 01/01/1900, " ","me.Value")

    in the actual query that provides the datasource I used ISNULL(sub.Date_Returned,'')
    LVL 142

    Accepted Solution

    what about this sql server expression:

    CASE WHEN sub.Date_returned < convert(datetime,  '1901-01-01' , 120) then NULL ELSE sub.date_returned end date_returned

    Author Comment

    Great stuff, thanks Angellll

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now