SQL Server 2000 View -- Case Statement

22 Jun 2006 4:38 PM

robboll

In a table I have a date format: YYYYMMDD

What do you do when instead of NULLs or Blank Dates there are 99999999?

In Access I'd use an IIF statement.

This case statement by itself doesn't work. Any ideas appreciated:

SELECT CASE WHEN Col004 = '99999999' THEN IS NULL ELSE
CONVERT(smalldatetime, Col004) AS MyDate
FROM dbo.MyTable;

Thanks,

RBollinger

22 Jun 2006 4:53 PM

Greg Larsen

Try this:

SELECT CASE WHEN Col004 = '99999999' THEN NULL ELSE
CONVERT(smalldatetime, Col004) end AS MyDate
FROM dbo.MyTable

Show quote

"robboll" wrote:

> In a table I have a date format: YYYYMMDD
>
> What do you do when instead of NULLs or Blank Dates there are 99999999?
>
> In Access I'd use an IIF statement.
>
> This case statement by itself doesn't work. Any ideas appreciated:
>
> SELECT CASE WHEN Col004 = '99999999' THEN IS NULL ELSE
> CONVERT(smalldatetime, Col004) AS MyDate
> FROM dbo.MyTable;
>
> Thanks,
>
> RBollinger
>
>

22 Jun 2006 9:02 PM

robboll

Thanks -- works great

Greg Larsen wrote:
Show quote

> Try this:
>
> SELECT CASE WHEN Col004 = '99999999' THEN NULL ELSE
> CONVERT(smalldatetime, Col004) end AS MyDate
> FROM dbo.MyTable
>
> "robboll" wrote:
>
> > In a table I have a date format: YYYYMMDD
> >
> > What do you do when instead of NULLs or Blank Dates there are 99999999?
> >
> > In Access I'd use an IIF statement.
> >
> > This case statement by itself doesn't work. Any ideas appreciated:
> >
> > SELECT CASE WHEN Col004 = '99999999' THEN IS NULL ELSE
> > CONVERT(smalldatetime, Col004) AS MyDate
> > FROM dbo.MyTable;
> >
> > Thanks,
> >
> > RBollinger
> >
> >

22 Jun 2006 5:55 PM

Roy Harvey

IS NULL is a test, an assignment is simply NULL. And CASE requires an
END.

SELECT CASE WHEN Col004 = '99999999'
            THEN NULL
            ELSE CONVERT(smalldatetime, Col004)
       END AS MyDate
FROM dbo.MyTable;

Roy Harvey
Beacon Falls, CT

Show quote

On 22 Jun 2006 09:38:01 -0700, "robboll" <robb***@hotmail.com> wrote:

>In a table I have a date format: YYYYMMDD
>
>What do you do when instead of NULLs or Blank Dates there are 99999999?
>
>In Access I'd use an IIF statement.
>
>This case statement by itself doesn't work. Any ideas appreciated:
>
>SELECT CASE WHEN Col004 = '99999999' THEN IS NULL ELSE
>CONVERT(smalldatetime, Col004) AS MyDate
>FROM dbo.MyTable;
>
>Thanks,
>
>RBollinger