How do I convert a SQL date to String date in different format?

Question

I have a SQL query result that is returning a date in this format ("yyyy-MM-dd hh:mm:ss"). My requirement is to convert it to a string (MM/yyyy) and put it in a Map for further comparisons. How Do I proceed to do that. So far this is what I have

  Map<String, String> postingDateAmountMap = new HashMap<>();
              postingDateAmountMap.put(rs.getString(3), rs.getString(2));

rs.getString(2) is returning date as 2021-03-01 00:00:00 whereas I need it as 03/2021 in the map.

NOTE: getString(3) and getString(2) are the ColumnIndex

Thanks in Advance.

Answer 1

tl;dr

Map< String , YearMonth > map = … ;
map.put( 
    rs.getString( 3 ) ,  // key
    YearMonth.from( 
        myResultSet.getObject( 2 , LocalDateTime.class )
    )                    // value
);

Use objects, not text

Retrieve smart objects from your database rather than dumb text.

LocalDateTime

If your column is of a type akin to the SQL-standard type TIMESTAMP WITHOUT TIME ZONE, use java.time.LocalDateTime.

Be aware that these types do not represent a moment as they purposely lack the context of a time zone or offset-from-UTC.

JDBC 4.2

JDBC 4.2 and later requires a JDBC driver to support the LocalDateTime type.

Call the ResultSet#getObject method rather than getString.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

YearMonth

Change your map to a key of String and a value of YearMonth. The YearMonth class represents, well, a year and a month. No day-of-month. Just what you are aiming for in your Question.

Map< String , YearMonth > map = new HashMap<>();
String k = rs.getString(3) ; // Named `k` for key of map entry.
LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ; 
YearMonth v = YearMonth.from( ldt ) ;  // Named `v` for value of map entry.
map.put( k , v );

Generating text

Later, when you want to present that YearMonth to the user as text in format of MM/YYYY, use DateTimeFormatter to generate text.

DateTimeFormatter f = DateTimeFormatter.ofPattern( "MM/uuuu" ) ;
String output = myYearMonth.format( f ) ;

Answer 2

hi RishiAutomation you can do it by the method split in one line as follow

postingDateAmountMap.put(rs.getString(3), rs.getString(2).split("-")[1] + "/" + rs.getString(2).split("-")[0]);

or in two lines as follow

String[] newDate = rs.getString(2).split("-");
postingDateAmountMap.put(rs.getString(3), newDate[1] + "/" + newDate[0]);