SQL Conversion failed when converting date and/or time from character string

Question

I am trying to set a column in a table to say 'no date set' if the column in the DB has a date value as NULL. If the column has a date value then i want to display it. This SQL is all within a SPROC and then the values are passed to the application

DB Values are of type DATETYPE,NULL

enter image description here

Here is what i have tried so far

,CASE WHEN [TB_EVNTEXP].[StartDate] IS NULL THEN 'no date set' ELSE [TB_EVNTEXP].[StartDate] END AS [expiry]

results in enter image description here

,COALESCE(CAST(NULL AS DATETIME), 'no date set'), [TB_EVNTEXP].[StartDate] AS [expiry]

Results in a conversion error.

This works but all null values are blank in my table.

,[TB_EVNTEXP].StartDate]                    AS [cardexpiry]

Spent way to much time on this now and could do with a helpful hand :)

Cheers Paul

When its like this it works ,[TB_EVNTEXP].StartDate] AS [cardexpiry] but when its like this it fails ,ISNULL([TB_EVNTEXP].[StartDate], 'no date set') AS [cardexpiry] — Paul
– Paul, Commented Jun 1, 2015 at 16:25

Tim3880 · Accepted Answer · 2015-06-01 16:24:55Z

3

It seems you are using SQL Server, which does not allow you mix string with Date format.

Try this:

SELECT CASE WHEN [TB_EVNTEXP].[StartDate] IS NULL THEN '' ELSE [TB_EVNTEXP].[StartDate] END AS [expiry] 
FROM

Or you need convert the date to a varchar to mix it with empty values:

SELECT CASE WHEN [TB_EVNTEXP].[StartDate] IS NULL THEN 'not set' ELSE CONVERT(VARCHAR, [TB_EVNTEXP].[StartDate]) END AS [expiry] 
FROM

answered Jun 1, 2015 at 16:24

Tim3880

2,5831 gold badge13 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Over a year ago

BOOM!! Thanks so much :) exactly what i needed! convert to a varchar. thanks Paul

Dan Bracuk · Accepted Answer · 2015-06-01 16:24:58Z

0

This is where your error occurs:

COALESCE(CAST(NULL AS DATETIME), 'no date set'),

This is logically the same, and it runs.

 COALESCE(NULL, 'no date set'),

or better yet,

'no date set'

answered Jun 1, 2015 at 16:24

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Collectives™ on Stack Overflow

SQL Conversion failed when converting date and/or time from character string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related