Convert varch(50) to yyyy-mm-dd

Question

Convert varch(50) to yyyy-mm-dd

SG-348334

Ten Centuries

Points: 1374
More actions
November 6, 2007 at 2:48 pm

#69198

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

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

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

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 1

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)

SG-348334 Ten Centuries Points: 1374 More actions · Answer 2

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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?

SG-348334 Ten Centuries Points: 1374 More actions · Answer 4

I was also able to do it with this

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