Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Struggling to use VarChar

Author  Topic 

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-10-08 : 11:25:59
HI,

I'm trying to get the following part of my query to run, but it says it cannot convert date and/or time from character string. COuld someone please tell me what I'm doing wrong please? Also, does the "<> 'NULL' " make sense or is there a better way of writing it.

CASE WHEN Vw_JW_06.expr1 <> 'NULL' THEN 'n/a' ELSE CONVERT(varchar(103), Vw_JW_05.expr1, 102) END AS [R+M3 Rec Unsigned],
or

CASE WHEN Vw_JW_06.expr1 <> 'NULL' THEN 'n/a' ELSE CAST(Vw_JW_05.expr1 AS varchar) END AS [R+M3 Rec Unsigned],


Basically Vw_JW_05 and Vw_JW_06 returns a date. If there is a date in Vw_JW_06 then I want to return 'n/a', else return Vw_JW_05.

I hope someone can understand that! :)

Jamie

Jim

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-08 : 12:09:47
NULL is a concept; not a (string) value. You want to test using IS NULL:
CASE WHEN Vw_JW_06.expr1 IS NULL THEN 'n/a' ELSE CAST(Vw_JW_05.expr1 AS varchar(10)) END AS [R+M3 Rec Unsigned],
Also, you can run into troubles if you don't define the size of the varchar when defining variables or in the CAST.



No amount of belief makes something a fact. -James Randi
Go to Top of Page

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-10-10 : 07:38:06
Hi Bustaz Kool and thank you. This works but the date format isn't right, its returning 4 Jun 2014 and I need it to return the format 04/06/2014. Do I need to change the varchar part?

Thanks again for your help.

J

Jim
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-10 : 07:48:36
Use CONVERT() instead of CAST. It lets you specify format string

CONVERT(varchar(10),Vw_JW_05.expr1,103)


Use british date formatting code - 103 - to denote dd/mm/yyyy format.

Harsh Athalye
http://in.linkedin.com/in/harshathalye/
Go to Top of Page

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-10-13 : 04:37:05
That works, thanks harsh_athalye and to Bustaz Kool also. I need to find a good source to read up on Varchar, Cast and Convert!

Thanks again.

Jim
Go to Top of Page
   

- Advertisement -