Link to home
Start Free TrialLog in
Avatar of RadhaKrishnaKiJaya
RadhaKrishnaKiJaya

asked on

Need help with a query

Hi Experts,

In the query if the ExpPurchaseDt="" then Formatted_ExpPurchaseDt is coming as "01/01/1900". Is there anyway I can prevent it in the query. I want if  ExpPurchaseDt="" then Formatted_ExpPurchaseDt should be "", otherwise it should format the date.

This is my query.
SELECT ExpPurchaseDt, CONVERT(VARCHAR, CONVERT(DATE, ExpPurchaseDt), 101) as Formatted_ExpPurchaseDt
FROM JOBDETAILS where JobOrOpportunity='O'

Thanks in advance.
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

You can use a case statement.  See this example:

DECLARE @date varchar(50)
SET @date = '2/5/2016'
SELECT CASE WHEN @date = '' THEN '' ELSE CONVERT(VARCHAR, CONVERT(DATE, @date), 101) END
SET @date = ''
SELECT CASE WHEN @date = '' THEN '' ELSE CONVERT(VARCHAR, CONVERT(DATE, @date), 101) END

Open in new window

>In the query if the ExpPurchaseDt=""
fyi this is one of the reasons a varchar column is a poor choice of data type to store date values, as it will allow values that cannot be converted to a date such as empty string '', 42, or 'banana', which will require queries to evaluate, convert it to a date format, and handle non-date values gracefully every time it needs to behave as a date.

Aside from that Dustin's comment above is the correct answer, just use a CASE block to pick it off.   For some extra reading on CASE check out the article SQL Server CASE
Avatar of RadhaKrishnaKiJaya
RadhaKrishnaKiJaya

ASKER

Thanks Mr. Jim for your suggestion.  When we do the next db design I will keep this thing in mind.
And in the previous question I  provided an article reference you should read regarding using text as dates.  It's not a good thing to do.
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much. This is what exactly I wanted. In next project I will definitely make any date's datatype datetime.