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.
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.
>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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. This is what exactly I wanted. In next project I will definitely make any date's datatype datetime.
Open in new window