Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Null date value in SQL Server view

    I have a SQL Server View. The problem is that the DateTime field has many Null values which is causing a problem with my parsing of the data in MSAccess.

    How would I use CAST (or CONVERT) to handle Null Date values in my SQL Server view?

    I remember there was a way to use CAST or CONVERT similar to the nz type function in MSAccess to handle null date values but I can't remember the syntax. What is happening now is that I get a data mismatch in my MSAccess function when it hits a Null Date value. Can I somehow use the ISNULL or ISDate function? I believe I need to somehow return "" instead of Null.
    Last edited by pkstormy; 05-24-07 at 13:36.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Code:
    COALESCE
     
    Returns the first nonnull expression among its arguments.
    Syntax
     
    COALESCE ( expression [ ,...n ] ) 
    Arguments
     
    expression
    Is an expression of any type.
    n
    Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible 
    to the same type.
    Return Types
     
    Returns the same value as expression.
    Remarks
     
    If all arguments are NULL, COALESCE returns NULL.
    COALESCE(expression1,...n) is equivalent to this CASE function:
    CASE   
    WHEN (expression1 IS NOT NULL) THEN expression1   ...   
    WHEN (expressionN IS NOT NULL) THEN expressionN   
    ELSE NULL
    END
    An example: set @dateval = coalesce (InDateVal,'1900-01-01')

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Actually, here's my view SQL:

    SELECT dbo.optimg05.doctp_desc, CAST(dbo.optimg01.user_key_9 AS DateTime) AS DateUsed, dbo.optimg01.user_key_44 AS EffectiveDate,
    dbo.optimg01.user_key_48 AS ContractNumber, dbo.optimg01.user_key_18 AS ContractorName, dbo.optimg01.user_key_52 AS PayrollNumber,
    dbo.optimg01.user_key_53 AS PayrollType, dbo.optimg01.pg_create_date, CAST(dbo.optimg01.pg_id AS int) AS intpgid
    FROM dbo.optimg01 INNER JOIN
    dbo.optimg05 ON dbo.optimg01.pg_doc_id = dbo.optimg05.doctp_id
    WHERE (dbo.optimg01.pg_folder_id = 11) AND (dbo.optimg01.pg_account_id = 9)

    what I want is for:

    CAST(dbo.optimg01.user_key_9 AS DateTime) AS DateUsed

    to return "" if Null, otherwise the date value in dbo.optimg01.user_key_9.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You cannot put an empty string into a datetime datatype ... what value do you want returned when optimg01.user_key_9 is null?

    If you insist on an empty string you will have to CAST/CONVERT it into a char or varchar datatype.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Perhaps the isnull function?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MCrowley
    Perhaps the isnull function?
    was already suggested, post #2

    actually, it was the COALESCE function, which i find preferable to ISNULL, on account of COALESCE is also standard sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    This is the link for Cast/Convert. You should bookmark that MSDN reference, it's a wealth of such syntax information as you are asking.

    It sounds like you just want to display/return the character equivilant of the date, since obviously you can't do date functions on space.

    So, you would want something like:

    select isnull( Convert(varchar(8),MyDate,101), '') DisplayDate from MyTable

    This will return a VarChar(8) date (format mm/dd/yyyy) or space.

Posting Permissions

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