Conver to varchar syntax 2

[jeffsturgeon2002]

I know I'm pretty close, but cant seem to get the syntax right

ISNULL(pp.birthdate,'')then convert(varchar(15),'No DOB'),

 

[bborissov]

CASE WHEN ISNULL(pp.birthdate,'') = ''
     then convert(varchar(15),'No DOB')
...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jeffsturgeon2002]

bborissov,

Im getting this back

Server: Msg 170, Level 15, State 1, Line 26
Line 26: Incorrect syntax near ','.

 

[jeffsturgeon2002]

helps when you "END" the case ... LOL

Thanks!!

 

[jeffsturgeon2002]

Spoke to soon .... the "END" stopped the syntax error, however the code you provided is now passing a NULL to everyone.

Thoughts?

CASE WHEN ISNULL(pp.birthdate,'') = ''
     	then convert(varchar(15),'No DOB')END,

 

[bborissov]

It would be better IF you post the full case. I changed ONLY the part you posted, because I don't know what happens next. Try:

CASE WHEN ISNULL(pp.birthdate,'') = ''
     then convert(varchar(15),'No DOB')
     ELSE pp.birthdate END AS birthdate,
...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Try this....


ISNULL(Convert(VarChar(20), pp.birthdate, 101),'No DOB')

If the birthdate is null, then converting it to varchar will still be null. And of course, the isnull will cause it to return 'NO DOB'.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jeffsturgeon2002]

George,

That worked like a charm. After the pp.birthdate, you list a "101" - what is this and do others exist that format datetimes? Still learning and like how you fixed this one.

 

[Catadmin]

Check in Books Online under CONVERT. There's an entire table of "styles" which format what you're converting and will give a better explaination than any of us ever could.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

Jeff, run this in query analyzer.

Select Convert(VarChar(20), GetDate(), 100)
Union All Select Convert(VarChar(20), GetDate(), 101)
Union All Select Convert(VarChar(20), GetDate(), 102)
Union All Select Convert(VarChar(20), GetDate(), 103)
Union All Select Convert(VarChar(20), GetDate(), 104)
Union All Select Convert(VarChar(20), GetDate(), 105)

You'll notice that the date is displayed several different ways.

Now, put the cursor on the Convert function and Press SHIFT-F1 on the keyboard. Books on line will display and you can see the different values that can be used.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jeffsturgeon2002]

Will do. Thanks!