How can I insert date in my SQL Server 2012?

Question

The date that is receive is in the format 'Wed Jan 01 02:00:00 IST 2020'.

If I use hibernate, it automatically converts this format into '2020-01-01 02:00:00' but for this case I have to use native query.

Is it possible in SQL SERVER 2012 to save the date 'Wed Jan 01 02:00:00 IST 2020' as the format '2020-01-01 02:00:00'?

Date and time data types don't have formats. It's up to your presentation to determine the format that they are **displayed** in. Data and Time data types are *stored* as binary values. — Thom A, Jan 07 '20 at 10:15

score 0 · Answer 1 · answered Jan 07 '20 at 10:53

The trick is to do the string to date conversion in Java.

@Transactional
public void insertWithQuery(String id, String firstName, String lastName,
                            String lastSeen) {
    // 'Wed Jan 01 02:00:00 IST 2020'
    DateTimeFormatter formatter = 
          DateTimeFormatter.ofPattern("EEE LLL dd HH:mm:ss zzz yyyy");
    java.time.ZonedDateTime dateTime = 
          ZonedDateTime.parse(lastSeen, formatter);
    entityManager.createNativeQuery(
            "INSERT INTO person (id, first_name, last_name, birth_date) " +
            "VALUES (?,?,?,?)")
        .setParameter(1, id)
        .setParameter(2, firstName)
        .setParameter(3, lastName)
        .setParameter(4, dateTime)
        .executeUpdate();
}

Note: this code is not tested. You may need to tweak the formatter's pattern. (I am going from what I think the javadoc says.)

The datetime stamp is stored in an SQL database in a format-independent way. By using setParameter you avoid having to convert the date into a text format that the native SQL dialect expects. Hibernate / JPA takes care of it.

How can I insert date in my SQL Server 2012?

1 Answers1