SQL Server Performance Forum – Threads Archive

SQL 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

---

I think you can use the same CONVERT logic to achieve this on second part of the query. replace CAST with CONVERT and use appropriate style ? ‘426.Ins1EffectiveDate’=CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardEffectiveDate,101),”)IS NULL THEN ” ELSE CONVERT(varchar(10),pi.InsCardEffectiveDate,101)END,
‘427.Ins1TerminationDate’= CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardTerminationDate,101),”)IS NULL THEN ” ELSE CONVERT(varchar(10), pi.InsCardTerminationDate,101)END
Name
———
Dilli Grg (1 row(s) affected)

---

Where do you want to show data? Madhivanan Failing to plan is Planning to fail

---

Please don’t overcomplicate things … CASE WHEN ISNULL(CONVERT(VARCHAR,pi.InsCardEffectiveDate,101),”)IS NULL
THEN ” ELSE CAST(pi.InsCardEffectiveDate AS varchar) END … can actually be shortened to … ISNULL(CONVERT(VARCHAR, pi.InsCardEffectiveDate, 101), ”) To drop the time part, convert to VARCHAR(10), and try the 110 switch: ISNULL(CONVERT(VARCHAR(10), pi.InsCardEffectiveDate, 110), ”) Use this for dumping data to a file or something. If you’re returning values to a client application, see if that application can handle the formatting.

---

]]>