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.
| 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! :)JamieJim |
|
|
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 |
 |
|
|
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.JJim |
|
|
|
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 stringCONVERT(varchar(10),Vw_JW_05.expr1,103) Use british date formatting code - 103 - to denote dd/mm/yyyy format.Harsh Athalyehttp://in.linkedin.com/in/harshathalye/ |
|
|
|
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 |
|
|
|
|
|
|
|
|
Struggling to use VarChar