0

I want to convert date which is string and like mm/dd/yy to date datatype in format yyyy/mm/dd.

RDP
  • 61
  • 2
  • 12
  • have you tried something? basically you will have to parse the string to a date using the first format a format to it back to a string using the second format. – A4L Apr 30 '14 at 18:06
  • possible duplicate of [How to set date format in sybase?](http://stackoverflow.com/questions/21013037/how-to-set-date-format-in-sybase) – mwigdahl Apr 30 '14 at 18:07
  • I tried using convert(char(10),'mm/dd/yy',101) but getting error – RDP Apr 30 '14 at 18:17

2 Answers2

1

I would double cast it, once to datetime, then back to varchar.

select convert(varchar,convert(datetime,'12/14/2012'),101)

This works on SQL Server, but I don't have a Sybase instance to test on.

Edit: Looks like you could also use this:

select convert(varchar,date('12/14/2012'),101)
Bacon Bits
  • 26,878
  • 5
  • 51
  • 60
  • Thanks. First query worked for me. But What will be the syntax for date is like 00/00/00. Above logic is not working for this. – RDP Apr 30 '14 at 19:29
  • Getting error as "Arithmetic overflow during explicit conversion of CHAR value '00/00/00' to a DATETIME field ." – RDP Apr 30 '14 at 19:34
  • @RDP If your date strings don't represent valid dates, type conversion can't fix that. I would use the `ISDATE()` function in your where clause to filter out invalid dates. You'll have to fix those some other way. http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc38151.1260/html/iqref/Isdate_misc_functions.htm – Bacon Bits Apr 30 '14 at 19:39
0

You have to first add a date field to your table:

alter table *your_table* add *newdate* as date;

Then you can convert using:

update *your_table* set *newdate* = cast(*your_string_date* as date);
Andy Korneyev
  • 25,238
  • 15
  • 65
  • 65
frenchie
  • 1