Results 1 to 3 of 3

Thread: Retrieving records with null-dates

  1. #1
    Join Date
    Feb 2009
    Location
    Denmark
    Posts
    335

    Default Retrieving records with null-dates

    I have converted an embedded database to MSSQL. I specified that Date should convert to Date (not DateTime). In this way the default value of Date is 01-01-0001.

    Now my problem is I cannot select records with empty dates. I have tried this (and some more):
    {Order.stopdate} = Date(1,1,1)
    IsNull{Order.stopdate}

    But I get nothing. It seems Crystal does not accept something like Date(1700,1,1). It must be at least Date(1753,1,1). Does Crystal work with another default value?

    Any suggestions how I solve the problem?
    Jørgen Münster
    Senior Analyst Programmer
    Dialog Consult
    Denmark

  2. #2
    Join Date
    Feb 2009
    Location
    Somewhere in Vermont, USA - unless I'm not
    Posts
    11,085

    Default Re: Retrieving records with null-dates

    If you are working with CR XI, it does not understand SQL Date fields nor DateTime2. You are limited to DateTime only.

    Date & DateTime2 came after XI - though later versions can use these types.

    DateTime has a minimum value of '1753-01-01' - the others are '0001-01-01'
    Garret

    Time for an oldie but goodie:

    "If it ain't broke, you're not trying." - Red Green

  3. #3
    Join Date
    Feb 2009
    Location
    Denmark
    Posts
    335

    Default Re: Retrieving records with null-dates

    The funny thing is that you can have a column in a report with the expression IsNull{Order.stopdate}, and it will print TRUE or FALSE correctly. Despite this you cannot have IsNull{Order.stopdate} in a select statement.

    After a lot of experiments I found out that this works when selecting records: ToText(isNull({Order.stopdate}))='TRUE'.

    It runs a little slower, but that is no problem.
    Last edited by Jørgen Münster; 21-Apr-2019 at 03:32 AM.
    Jørgen Münster
    Senior Analyst Programmer
    Dialog Consult
    Denmark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •