Converting a integer to date

Question

I am having an issue with converting an integer date (20180525) to a date in the format of YYYY-MM-DD. Is there a way to do this or should I just convert it in code (Java in this case)? Any help with this would be most appreciated. Thank you!

Answer 1

if your dates are stored as integers with YYYYMMDD, you can pass it to the date function by first casting to TEXT.

SELECT date(20180525::TEXT)

otherwise, use the function to_date with a date formatter:

SELECT to_date(20180525::text, 'YYYYMMDD')

Answer 2

tl;dr

Either SQL or Java works well. For SQL, see the correct Answer by Ali.

For Java, pass a LocalDate to a PreparedStatement query.

LocalDate.parse(                      // `LocalDate` represents a date-only value without time-of-day and without time zone. Works with SQL-standard `DATE` type.
    Integer.toString( 20180525 ) ,    // Convert integer to text, for a `String` object as input.
    DateTimeFormatter.BASIC_ISO_DATE  // Specify a formatting pattern to match our input string.
)                                     // Returns a `LocalDate` object.

2018-05-25

java.time

Regarding Java mentioned in the Question, here is code using the modern java.time classes. Whether you should use SQL or Java to parse you string depends on your situation and your tastes, as either route works.

LocalDate

The LocalDate class represents a date-only value without time-of-day and without time zone.

You need to specify a formatting pattern to match your input. In this particular case, your input string is in standard ISO 8601 format. Specifically, the “basic” variant of ISO 8601 formats that minimize the use of delimiters. In java.time, you will find this particular formatting pattern is conveniently predefined for you.

String input = Integer.toString( 20180525 ) ;  // Convert integer to text for input as `String`. 
DateTimeFormatter f = DateTimeFormatter.BASIC_ISO_DATE ;
LocalDate ld = LocalDate.parse( input , f ) ;

ld.toString(): 2018-05-25

JDBC

As of JDBC 4.2 and later, you can directly exchange java.time objects with your database. Always better to use smart objects rather than dumb strings.

String sql = "SELECT event_date FROM tbl WHERE event_date = ? ; " ;
…
myPreparedStatement.setObject( 1 , ld ) ;

And retrieval.

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, Java SE 10, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 3

select ((field_int)::text)::date as field_date from your_table;

Answer 4

Not correct answer is to_timestamp(timestamp_int)::date, but it's really what i was expected to find under this title/question.
Explanation that someone save date as int 20180525 shows that the world is full of incredible ideas