CODEWITHSUNDEEP
sqlsql-server-2008
Ozbear
Ozbear

Reputation: 59

SQL Output Date or nothing

Have had a look but couldn't find anything.

I am trying to run a query to get results of a selection of records and there is a date field on the records but I only want to output the date when certain criteria is met and if not met then I want nothing output.

I have tried Case When but i still get an actual date, not the original date, output.

The script is to be used in Excel to extract a report and just want to hold back useless information if I can.

So am trying the following:

   SELECT Pickup_Date,
       Pickup_Time,
       Pickup,
       CASE
           WHEN Product_Service = 'AC' THEN DROPOFF_DATE
           WHEN Product_Service = 'CR' THEN DROPOFF_DATE
           WHEN Product_Service = 'FL' THEN DROPOFF_DATE
           ELSE ''
       END AS DropoffDate,
       Dropoff_Time,
       Dropoff,
       Remarks,
       Agent_Price_Inc,
       Service_Cost_Inc
FROM OPSView

The DROPOFF_DATE field is a datetime type.

Is there anyway to be able to do this?

Any assistance appreciated.

Upvotes: 1

Views: 73

Answers (2)

Ralf de Kleine
Ralf de Kleine

Reputation: 11744

Returning VarChar doesn't mix with returning a DataTime. Use 'NULL' instead of empty varchar ''.

You can validate by testing:

select getdate()
union
select null

--Result
NULL
2013-12-30 11:17:08.517

Instead of:

select getdate()
union
select ''

--Result:
1900-01-01 00:00:00.000
2013-12-30 11:17:39.577

Answer to your question:

SELECT Pickup_Date,
       Pickup_Time,
       Pickup,
       CASE
           WHEN Product_Service = 'AC' THEN DROPOFF_DATE
           WHEN Product_Service = 'CR' THEN DROPOFF_DATE
           WHEN Product_Service = 'FL' THEN DROPOFF_DATE
           ELSE NULL -- Return null instead of ''
       END AS DropoffDate,
       Dropoff_Time,
       Dropoff,
       Remarks,
       Agent_Price_Inc,
       Service_Cost_Inc
FROM OPSView

Upvotes: 4

Nalaka526
Nalaka526

Reputation: 11464

Try,

CASE
       WHEN Product_Service = 'AC' THEN CAST(DROPOFF_DATE AS VARCHAR)
       WHEN Product_Service = 'CR' THEN CAST(DROPOFF_DATE AS VARCHAR)
       WHEN Product_Service = 'FL' THEN CAST(DROPOFF_DATE AS VARCHAR)
       ELSE ''
END AS DropoffDate,

Upvotes: 0

Related Questions