How to convert date to sql date

How to Convert java.util.Date to java.sql.Date in Java?

Date class is present in both java.util package and java.sql package. Though the name of the class is the same for both packages, their utilities are different. Date class of java.util package is required when data is required in a java application to do any computation or for other various things, while Date class of java.sql package is used whenever we need to store or read the data of DATE type in SQL, also Date class of java.sql package stores information only regarding the date, whereas Date class of java.util package stores both date and time information.

It must be remembered that when we need to convert one data form to another, we must use getTime() method of the Date class of java.util package.Though java.sql.Date class is a subclass of java.util.Date class, we can’t use java.sql.Date class wherever java.util.Date class must be passed, else it will violate the Liskov Substitution principle and our program will throw run time errors on execution, therefore it is not advised to pass SQL Date to methods that expect util date. Let us do discuss getTime() method prior to landing upon the implementation part.

The getTime() method of Java Date class returns the number of milliseconds since January 1, 1970, 00:00:00 GTM which is represented by the Date object.

Syntax:

Parameters: The function does not accept any parameter.

Return Value: It returns the number of milliseconds since January 1, 1970, 00:00:00 GTM.

Exception: The function does not throw any exceptions.

I really hope that the JDBC driver could do the translation depending upon the data type of columns and Java developer could just pass the java.util.Date I mean, convert it to java.sql.Date if the column in the table is of type DATE, java.sql.Time if the type of column is TIME and java.sql.Timestamp if the data type of column is DATETIME.

Unfortunately, there is no method like toSQLDate() in java.util.Date class to facilitate conversion between util date and SQL date but you can use getTime() method to extract long millisecond value from java.util.Date and create a new java.sql.Date based on that value as shown below:

This is the easiest and right way to convert a java.util.Date to java.sql.Date in Java. To learn more about JDBC and how to write Java application that connects to the database, please see Core Java, Volume II—Advanced Features (10th Edition), one of the best books to learn advanced features of Java programming language.

Things to remember about SQL and Normal Date in Java

1) java.sql.Date mapped to DATE datatype on JDBC i.e. Type.Date, which corresponds to date equivalent in DB side. In SQLSERVER till 2008 it maps to DATETIME and afterward maps to DATE data type.

2) java.sql.Date extends java.util.Date but violets Liskov Substituion Principle.

3) You can convert between java.util.Date and java.sql.Date by using getTime() method.

4) Here is mapping between MySQL date and time types and Java Date types:

SQL Query to Convert Datetime to Date

In MS SQL Server, dates are complicated for newbies, since while working with the database, the format of the date in the table must be matched with the input date in order to insert. In various scenarios instead of date, DateTime (time is also involved with date) is used. In this article, we will learn how to convert a DateTime to a DATE by using the three different functions.

The aim of this article data is to convert DateTime to Date in SQL Server like YYYY-MM-DD HH:MM: SS to YYYY-MM-DD.

Method 1: Using cast

This is a function for casting one type to another type, So here we will use for cast DateTime to date.

Syntax:

Example 1:

Query:

Output:

GETDATE(): This function return current date time like(2021-08-27 17:26:36.710)

Example 2;

Query:

Output:

Method 2: Using Convert

This is a function for convert one type to another type, So here we will use it to convert DateTime to date.

Syntax:

Example 1:

Query:

Output:

Example 2:

Query:

Output:

Method 3: Try_Convert

This is a function for casting one type to another type, So here we will use for Convert DateTime to date. if the date is invalid then it will be null while Convert generates an error.

Syntax:

SELECT TRY_CONVERT(DATE,’2021-08-27 17:26:36.710′) AS CURRENT_DATE_GFG

Example 1:

Query:

Output:

Example 2:

Query:

Output:

Method 4: Using Substring

This is a function to use get a short string or substring, so here use we get substring 0 to 11 index.

Database.Guide

Beginners

Categories

Examples of Converting ‘date’ to ‘datetime’ in SQL Server (T-SQL)

This article contains examples of converting a date value to a datetime value in SQL Server.

When you convert a date value to datetime, extra information is added to the value. This is because the datetime data type contains both date and time information. The date data type, on the other hand, only contains date information.

Example 1 – Implicit Conversion

Here’s an example of an implicit conversion between date and datetime.

This is an implicit conversion because we’re not using a conversion function (like the ones below) to explicitly convert it. In this case, SQL Server performs an implicit conversion behind the scenes when we try to assign the date value to a datetime variable.

We can see that the date variable only contains date information, whereas, the datetime variable contains both date and time information.

Example 2 – Modify the Time

If you need to change the time (but keep the same date), you can use the DATEADD() function to do just that.

Example 3 – Explicit Conversion using CAST()

Here’s an example of an explicit conversion. In this case, I use the CAST() function directly within the SELECT statement to explicitly convert between date and datetime.

So we get the same result as the implicit conversion.

We can also adjust the time like this:

Example 4 – Explicit Conversion using CONVERT()

Database.Guide

Beginners

Categories

How to Convert from One Date Format to Another in SQL Server using CONVERT()

Sometimes when working with databases and other programming environments, you get a date value but it’s in the wrong format/data type. For example, if a date has been generated with an inbuilt date function, it might include both the date and the time, right down to the last nanosecond. And all you want is the day, month, and year, say like this: 2018-01-01.

If this happens while you’re using SQL Server, you can use the CONVERT() function to convert it to another data type. When you do this, the data type will determine the format it appears as.

This article provides examples of using the CONVERT() function in SQL Server to convert a date value to another (date) data type.

Syntax

First, here’s how the official syntax goes:

These arguments are defined as follows:

Example 1 – Convert from SYSDATETIME() to date Data Type

In this example, we generate the current date/time with the SYSDATETIME() function and convert that to a date data type.

Note that the SYSDATETIME() generates its value as a datetime2(7) data type, so we are converting it from that data type to another data type.

Example 2 – Convert from SYSDATETIME() to smalldatetime Data Type

In this example, we convert the date to a smalldatetime data type.

Example 3 – Convert from SYSDATETIME() to datetimeoffset Data Type

In this example, we convert the date to a datetimeoffset data type.

Example 4 – Convert from SYSDATETIME() to time Data Type

You aren’t limited to displaying the date component of the value. You can also convert it to a time data type, so that only the time component is returned. Like this:

Example 5 – Convert from SYSDATETIMEOFFSET()

The previous examples all use the same inbuilt function to generate the date/time value, but of course, it doesn’t need to be generated by this one function. Here’s an example using the SYSDATETIMEOFFSET() :

Example 6 – Convert from a Database Query

Here’s an example of converting the date that’s retrieved from a column in the WideWorldImporters sample database:

Note that you’re not limited to just converting between two different date formats. If you have a date stored as a string for example, you can also use CONVERT() to convert from a string to a date, as well as any other data type you might need to convert to.

I’ve also written a post that shows how to convert between date formats using the CAST() function using the same examples as above.