Converting varchar to date in format (mm/dd/yyyy)

I am running a query that converts varchar column into a date. But right now its returning 31/06/2015  for example. I need it to be converted into mm/dd/yyyy format.

CAST(dob AS TIME)  AS DobD

Help is appreciated.
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
AleksAuthor Commented:
So you are saying to use:

        convert(datetime, StatusExpires ,101)AS ExpiresOnD ,

Instead of:

        CAST(StatusExpires AS TIME)  AS ExpiresOnD ,
AleksAuthor Commented:
I tried:           CONVERT(datetime, StatusExpires ,101)AS ExpiresOnD ,

the date still shows as 31/07/2015  :$
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AleksAuthor Commented:
This is the whole SQL maybe this makes more sense of what I am trying to do:


SELECT  
		IndivID AS useridtracker ,
        '1' AS Firmid ,
        'contact' AS usertype ,
        CAST( IFirst AS VARCHAR(30)) AS FirstNm ,
        IMiddle AS MiddleNm ,
        CAST( ILast AS VARCHAR(30)) AS LastNM ,
        '1' AS contactstatus ,
        '1' AS FirmaddressIdcon ,
        '0' AS ordernum ,
        ITitle AS Position ,
        EmpID AS EmployerID ,
        CAST( OfficeAddress AS VARCHAR(200)) AS MailStr ,
        CAST( OfficeApt AS VARCHAR(10)) AS MailApt ,
        CAST( OfficeCity AS VARCHAR(40))  AS MailCity ,
        CAST( OfficeState AS VARCHAR(20)) AS MailState ,
        CAST( OfficeZip AS VARCHAR(20)) MailZip ,
        CAST( OfficeCountry AS VARCHAR(40)) MailCntry ,
        CAST( HomeAttn AS VARCHAR(50)) AS ResCareOf ,
        CAST( HomeAddress AS VARCHAR(200)) AS ResStr ,
        CAST( HomeApt AS VARCHAR(10)) AS ResApt ,
        CAST( HomeCity AS VARCHAR(40)) AS ResCity ,
        CAST( HomeState AS VARCHAR(20)) AS ResState ,
        CAST( HomeZip AS VARCHAR(20)) AS ResZip ,
        CAST( HomeCountry AS VARCHAR(40)) AS ResCntry ,
        CAST( ForeignAddress AS VARCHAR(50)) AS AbrdStr ,
        CAST( ForeignApt AS VARCHAR(10)) AS AbrdApt ,
        CAST( ForeignCity AS VARCHAR(40)) AS AbrdCity ,
        CAST( ForeignState AS VARCHAR(20)) AS AbrdState ,
        CAST( ForeignZip AS VARCHAR(20)) AS AbrdZip ,
        CAST( ForeignCountry AS VARCHAR(40)) AS AbrdCntry ,
        CAST( HomePhone AS VARCHAR(25)) AS EvePhone ,
        CAST( OfficePhone AS VARCHAR(25)) AS DayPhone ,
        CAST( OfficeFax AS VARCHAR(14)) AS Fax ,
        CAST( OfficeEMail AS VARCHAR(80)) AS email ,
        CAST( CellPhone AS VARCHAR(25))  AS celphone ,
        CAST( Nationality AS VARCHAR(40))  AS Nationality ,
        CAST( dob AS VARCHAR(25)) AS Dob ,
        CAST(dob AS TIME)  AS DobD ,
        MainStaffContact AS ParalegalID ,
        Atty AS Attyid ,
        Active AS Archivedcont ,
        INotes AS Notes ,
        PublicNotes AS Notes2 ,
        CAST( BirthCountry AS VARCHAR(40)) AS POBCountry ,
        CAST( CurrentStatus AS VARCHAR(40)) AS NiStatus ,
        CAST( StatusExpires AS VARCHAR(25)) AS ExpiresOn ,
        CONVERT(datetime, StatusExpires ,101)AS ExpiresOnD ,
        CAST( I797Expires AS VARCHAR(20)) AS I797Date ,
        CONVERT(datetime, I797Expires ,101)AS I797DateD,
        CAST( VisaExpires AS VARCHAR(25)) AS VisaExp ,
        CONVERT(datetime, VisaExpires ,101) AS VisaExpD ,
        CAST(VisaMax AS TIME) AS NIVMaxStatus ,
        CAST( EADExpires AS VARCHAR(25)) AS EadDate ,
        CONVERT(datetime, EADExpires ,101) AS EadDateD ,
        CONVERT(datetime, APExpires ,101)AS ApDate ,
        CONVERT(datetime, EADAPExpires ,101)AS AddAPExpires ,
        CONVERT(datetime, PriorityDate ,101)AS AddPriorityDate ,
        PrefNbr AS Notes3 ,
        CAST( Salutation AS VARCHAR(80)) AS Title,
        '1' AS Imported
        
INTO BlueDotUsersMaincontacts
FROM dbo.Individual;

Open in new window

Ray PaseurCommented:
Please see this article, especially if you are using PHP.  The ISO-8601 standard helps us forgive a multitude of sins!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
arnoldCommented:
What is the format of the data before your conversion?



Here is the conversion map:  https://msdn.microsoft.com/en-us/library/ms187928.aspx
you might have to go through convert twice, once going from varchar to datetime, the other changing the resulting format
Convert(datetime,convert(datetime,StatusExpires,103),101)
Anoo S PillaiCommented:
Since your source columns are VARCHAR, convert them into DATE first, Then do a conversion using CONVERT with the required format string to VARCHAR type. The following will clarify the concept  
DECLARE @StatusExpires   VARCHAR(12)  = '20150630'
SELECT	CONVERT(VARCHAR(10) , CAST ( @StatusExpires AS DATE )  ,101) AS StatusExpiresConverted . The second one is the current scenario as per your current query

DECLARE @EADAPExpires   VARCHAR(12)  = '20150630'
SELECT	CONVERT(VARCHAR(10) , @EADAPExpires ,101) AS StatusExpiresConverted

Open in new window

AleksAuthor Commented:
Thanks. Ill first cast to date then try the convert to correct format. And I am using ASP.
arnoldCommented:
You can either convert on the SQL using the query, or convert the data into any format you want once you have the sata in asp.
is there validation on the input side to make sure the data is entered uniformly?
I.e. 03/02/2015 February 3rd 2015 ; 31/12/2014  dec 31 2014.
Anoo S PillaiCommented:
@arnold - Think yours is a very valid comment ( "is there validation on the input side to make sure the data is entered uniformly" ) , Reason being the sample value provided in the question "31/06/2015" is not a valid date  ( June 31st , hmm that is not my salary day anyway. My salary would be credited on June 30   :)  )
AleksAuthor Commented:
This is data import. I had no control over how the data was entered in the other system  :$
AleksAuthor Commented:
I ran this and an error is displayed on the second line.

  CAST( CurrentStatus AS VARCHAR(40)) AS NiStatus ,
            CONVERT(VARCHAR(10) , CAST ( StatusExpires AS DATE )  ,101) AS ExpiresOn
        CAST(StatusExpires AS TIME)  AS ExpiresOnD ,
AleksAuthor Commented:
This didn't work either:   CAST((StatusExpires AS DATE),101)  AS ExpiresOnD ,
arnoldCommented:
Your sevond line convert sshould use datetime not varchar
PortletPaulEE Topic AdvisorCommented:
Try these:
select
    DobD
from Individual
where isdate(DobD) = 0
;

select
      case when isdate(DobD) = 0 then '' else
               convert(varchar(10),convert(date,DobD,103),101) end
    , DobD
from Individual
;

Open in new window

The first will list those rows that have strings that are not recognized as dates
The second uses a case expression to avoid conversion issues of bad date strings.

The point is that there is no 31st of June in any year, so that data needs fixing before you can successfully change the output into a MM/DD/YYYY format.

I know it has already been said, but storing date/time information as strings always has this awful potential for bad data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.