Solved

Need help with a query

Posted on 2016-10-27
6
66 Views
Last Modified: 2016-10-28
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.
0
Comment
Question by:RadhaKrishnaKiJaya
6 Comments
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41862870
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

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41862874
>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
1
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41862898
Thanks Mr. Jim for your suggestion.  When we do the next db design I will keep this thing in mind.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 41862926
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.
0
 
LVL 23

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41863244
Informational , WITH 2012+ IIF is also there which handles these kind of scenarios.

Also note that always provide proper LENGTH while conversions and declaring variables like below
CONVERT(VARCHAR(10), CONVERT(DATE, @date), 101))

DECLARE @date VARCHAR(10)

SET @date = '2/5/2016'
SELECT IIF ( @date = '' , '' ,CONVERT(VARCHAR(10), CONVERT(DATE, @date), 101)) Formatted_ExpPurchaseDt

SET @date = ''
SELECT IIF ( @date = '' , '' ,CONVERT(VARCHAR(10), CONVERT(DATE, @date), 101)) Formatted_ExpPurchaseDt


--Code for your query

SELECT ExpPurchaseDt, IIF ( ExpPurchaseDt = '' , '' ,CONVERT(VARCHAR(10), CONVERT(DATE, ExpPurchaseDt), 101)) Formatted_ExpPurchaseDt
FROM JOBDETAILS where JobOrOpportunity='O'

Open in new window


Also if you can change the data type of the date column to DateTime/Date that would be good!!

Thnx!
0
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 41864038
Thank you very much. This is what exactly I wanted. In next project I will definitely make any date's datatype datetime.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now