DateTime help

I am having issues with Two lines in my Stored Procedure:

'426.Ins1EffectiveDate'=CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardEffectiveDate,101),'')IS NULL THEN '' ELSE CAST(pi.InsCardEffectiveDate AS varchar)END,
'427.Ins1TerminationDate'= CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardTerminationDate,101),'')IS NULL THEN '' ELSE CAST(pi.InsCardTerminationDate AS varchar)END

Its returning a date value as such:

Mar 12 2008 12:00AM

I dont want the 12:00 AM ... in fact, I want it to read 03/12/2008

Give a look to the CAST AND CONVERT article in books online. You ought to be able to change the ",101" date format specification to fix this problem.

Maybe try converting this:

Code Snippet

'426.Ins1EffectiveDate'=CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardEffectiveDate,101),'')IS NULL THEN '' ELSE CAST(pi.InsCardEffectiveDate AS varchar)END,
'427.Ins1TerminationDate'= CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardTerminationDate,101),'')IS NULL THEN '' ELSE CAST(pi.InsCardTerminationDate AS varchar)END

into this:

Code Snippet

'426.Ins1EffectiveDate'=CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardEffectiveDate,101),'')IS NULL THEN '' ELSE convert(varchar,pi.InsCardEffectiveDate,101)END,
'427.Ins1TerminationDate'= CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardTerminationDate,101),'')IS NULL THEN '' ELSE convert(varchar,pi.InsCardTerminationDate,101)END

After you try this, check back because it looks like you have another logic problem. Something along the lines of this:

Code Snippet

'426.Ins1EffectiveDate'=CASE when pi.InsCardEffectiveDate IS NULL THEN '' ELSE convert(varchar,pi.InsCardEffectiveDate,101)END,
'427.Ins1TerminationDate'= CASE WHEN pi.InsCardTerminationDate IS NULL THEN '' ELSE convert(varchar,pi.InsCardTerminationDate,101)END

might be more appropriate

|||

It seems you are making this overly complicated. (Unless I'm missing something...)

I don't think that you need to use the CASE structure...

JeffS2002 wrote:

'426.Ins1EffectiveDate'=CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardEffectiveDate,101),'')IS NULL THEN '' ELSE CAST(pi.InsCardEffectiveDate AS varchar)END,
'427.Ins1TerminationDate'= CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardTerminationDate,101),'')IS NULL THEN '' ELSE CAST(pi.InsCardTerminationDate AS varchar)END

Perhaps something more like this:

'426.Ins1EffectiveDate' = isnull( convert( varchar(10), pi.InsCardEffectiveDate, 101 ), '' )

Follow this example:

Code Snippet

DECLARE
@.InsCardEffectiveDate datetime,
@.NullDate datetime

SET @.InsCardEffectiveDate = '20070711'

SELECT
NullDate = isnull(( convert( varchar(10), @.NULLDate, 101 )), '' ),
InsCardEffectiveDate = isnull(( convert( varchar(10), @.InsCardEffectiveDate, 101 )), '' )

NullDate InsCardEffectiveDate
- --
07/11/2007