0

I couldn't pass in 2019-08-07T14:00:00-0400 to a stored procedure in SQL Server that takes a param in DATETIME.

So how can I convert it to this format YYYY-MM-DD HH:MM:SS.SSS in Java prior to calling the stored procedure?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
isoplayer
  • 67
  • 2
  • 7
  • 1
    If you want to convert it in Java then why tag SQL Server? – Dale K Aug 20 '19 at 05:32
  • 3
    `I couldn't pass ...` What have you tried to do? Are you trying to pass a string? What happened? Seeing the actual code will help us to understand your problem. – default locale Aug 20 '19 at 05:33
  • I think it is already answered [here](https://stackoverflow.com/questions/15433377/how-parse-2013-03-13t2059310000-date-string-to-date). Please try this. – Sreepu Aug 20 '19 at 05:38
  • I'm trying to pass in a string `2019-08-07T14:00:00-0400` in Java to a stored proc, and convert that to 121 style. I tagged sql server because I'm open to both java solution and sql solution. – isoplayer Aug 20 '19 at 05:56
  • 2
    Don't pass datetime values as Strings. Use a [PreparedStatement](https://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/) and pass them as instances of `LocalDateTime` –  Aug 20 '19 at 06:15
  • `-0400` in your string is a timezone offset. Can `datetime` in SQL Server include it? Or does it always assume UTC, for example? Your desired format, `YYYY-MM-DD HH:MM:SS.SSS`, seems to indicate that you will be losing this crucial piece of information. – Ole V.V. Aug 20 '19 at 06:34

2 Answers2

1

You can make use of SimpleDateFormatclass for reference visit here

SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ssZ");
Date date = df.parse("2019-08-07T14:00:00-0400");

You will get java.util.Date object in return which in turns you can use to store in database.

I'm_Pratik
  • 541
  • 8
  • 22
  • 1
    Don't use `java.util.Date` - you should **at least** use `java.sql.Timestamp` but `java.time.LocalDateTime` would be the preferred solution. –  Aug 20 '19 at 06:16
  • 2
    Please don’t teach the young ones to use the long outdated and notoriously troublesome `SimpleDateFormat` class. At least not as the first option. And not without any reservation. Today we have so much better in [`java.time`, the modern Java date and time API,](https://docs.oracle.com/javase/tutorial/datetime/) and its `DateTimeFormatter`. – Ole V.V. Aug 20 '19 at 06:32
  • @OleV.V. can you share your thoughts and suggest a better solution for specified format? – I'm_Pratik Aug 20 '19 at 11:42
  • I would like to write an answer in line with the comment by @a_horse_with_no_name using a datetime object such as `LocalDateTime` rather than a string; but I don’t know SQL Server and its JDBC driver well enough to be sure that `LocalDateTime` is the exact correct Java type (it does sound correct). So I won’t. – Ole V.V. Aug 20 '19 at 12:50
0

If you want to convert it in SQL queries you can use below query.

 declare @abc nvarchar(19)='2019-08-07T14:00:00-0400'
 select CONVERT(DATETIME,convert(varchar, @abc,121))
Sreepu
  • 123
  • 1
  • 1
  • 14
  • In SQL Server, *never* use `char`, `varchar` and so on without lengths! The defaults vary by context and can lead to unexpected and very hard-to-debug errors. – Gordon Linoff Aug 20 '19 at 11:59