Convert varch(50) to yyyy-mm-dd

  • We have a 3rd party application that connects to our SQL 2005 as a external database source.

    Our external database has a 'datein' field that for some reason is stored as a varchar(50).

    I need to convert this field to a format of YYYY-MM-DD in order for the 3rd party application to search on it correctly. It would be pretty straight forward if it was a datetime field, but I'm confused on how to deal with date data as a varchar..

    Thanks

    Below is an example of the data --

    FieldName = DateIn

    DateType = Varchar(50)

    2006-07-06

    Sep 12 2007 11:16AM

    2007-07-19

    Oct 2 2007 12:24PM

    2007-08-19

    Oct 31 2007 8:00PM

    Sep 15 2007 9:25AM

    2007-08-18

    Oct 16 2007 7:01PM

    Oct 30 2007 11:15AM

    Aug 29 2007 8:54AM

    Aug 29 2007 8:38PM

    Aug 25 2007 2:33PM

  • It's simple... convert all of those formats to datetime using just CAST... they'll all "fly" correctly... and convert them back to your YYYY-MM-DD format...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The cast works to modify the field to the smalldatetime, but how I would use the convert in the same select statement?

    Thanks for your help

  • with the dashes - that's a non-standard format... Try this...

    select replace(convert(char,cast(datein as smalldatetime),111),'/','-')....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I was also able to do it with this

    select convert(char(10),cast(DateIn as datetime),120)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply