Obtain a date that does not contain the time section in SQL + Oracle

In this article, the date obtained by getdate () is composed of two parts: today's date and then time: Select getdate ()

Use datename () to obtain the corresponding year, month, and day, and then connect them:

Select datename (year, getdate () + '-' + datename

(Month, getdate () + '-' + datename (day, getdate ())

In addition, datename () can also be used to obtain the hour, time, second, day of week, and week number, as shown below:

Select datename (hour, getdate ())

Select datename (minute, getdate ())

Select datename (second, getdate ())

Select datename (weekday, getdate ())

Select datename (Week, getdate ())

The default format of datetime in SQL is yyyy-mm-dd hh: MI: SS: Mmm. In most cases, we only want to get its date part, instead of the next time. As mentioned in the previous article, we used the datename () function to extract the date that does not contain the time part. Now we can use another method to obtain a date that does not contain the time!

Use the convert () function:

Select convert (char (10), getdate (), 120) as date

* The 3rd parameters are used to set the display style of the date data. The following describes several style parameters:

100mm dd yyyy

101/DD/YYYY

102 YYYY. Mm. dd

103 dd/mm/yyyy

106 DD MM YYYY

108 hh: MI: SS (time)

111 yyyy/mm/dd

112 yyyymmdd

120 yyyy-mm-dd

You can find that it is more convenient to use this method to obtain the date part. Compared with this method, the advantage of the datename () function is to obtain the specific part of the date.

In the Oracle database, if the field is of the Data Type, only a certain segment of the field is taken out. Here we use the tcrq table (production date, date type) as an example.

If both the date and time values are obtained, select * from table name directly, as long as the date: String AA = "select to_char (tcrq, 'yyyy-mm-dd ') as tcrq from fw_jcxxb "; time is the same.