• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

Date format conversion

In Sybase 20070208      
In Oracle  08/02/2007 00:00

What query can i use to make them equal strings. I am dumping this data/table into CSV. So the conversion must happen in select statement itself. After conversion they must be string1.equalsIgnoreCase(string2) must return true
0
PearlJamFanatic
Asked:
PearlJamFanatic
  • 4
  • 3
  • 2
  • +3
5 Solutions
 
CEHJCommented:
Just format it. What format do you want. The most rational one is ISO, which you can do with
java.sql.Date d = rs.getDate("datefield");
String cell = d.toString(); //2007-02-30

Open in new window

0
 
objectsCommented:
simplest would be to use the date formatting functions provided by the database to format them in the select
0
 
objectsCommented:
That way you can use the result set directly, lot simpler than messing around formating them on the fly
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
PearlJamFanaticAuthor Commented:
objects: yes I want to do this in the select itself. Please give an example of the select statement that does the conversion.
0
 
objectsCommented:
we rarely use oracle or sybase. have a look at the links above, should be straight forward
0
 
CEHJCommented:
Bear in mind that using the db will make your code unportable. You could do it all in Java

http://opencsv.sourceforge.net/apidocs/au/com/bytecode/opencsv/CSVWriter.html#writeAll(java.sql.ResultSet, boolean)
0
 
slightwv (䄆 Netminder) Commented:
In Oracle you can convert a Date to any string imaginable with TO_CHAR and a format mask.

To convert an Oracle date to the Sybase format:
select to_char(sysdate,' YYYYMMDD')

Note that Oracle date datatypes have a time portion built in.  If you use the format above you will lose it.
0
 
slightwv (䄆 Netminder) Commented:
0
 
wilcoxonCommented:
In Sybase, you can use convert.  Specifically, to get YYYYMMDD, it is:

select convert(char(8), getdate(), 112)
-- just replace getdate() in the above with whatever date field you want.
0
 
wilcoxonCommented:
Oops.  I read that backwards.  Your Sybase date is already in YYYYMMDD format.  To convert a Sybase date to DD/MM/YYYY H24:mm, you can do:

select convert(char(10), getdate(), 101)+' '+convert(char(5), getdate(), 108)

There is no exact format that matches what Oracle gives so I just constructed one from two converts.
0
 
awking00Commented:
convert(char(8),sybasedate,112) = to_char(oracledate,'yyyymmdd')
0
 
CEHJCommented:
:)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now