November 6, 2007 at 2:48 pm
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
November 6, 2007 at 2:54 pm
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
Change is inevitable... Change for the better is not.
November 6, 2007 at 3:24 pm
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
November 6, 2007 at 3:52 pm
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?
November 6, 2007 at 4:50 pm
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