Date--sql Query

Scenario: A record is deleted in the database, but the uniqueness of the record can only be determined by inserting the database's system time, which is the date type, in the format: 2016/10/19 17:17:29.

1 resolution

The method found on Baidu is to use the To_char () function to convert the Date field to a character for querying.

Delete from Trndetail where transno=10010617 and To_char (Sysdtime, ' yyyy/mm/dd hh24:mi:ss ') = ' 2016/10/19 17:17:29 ';

2 Supplemental 2.1 Conversion functions

The most significant relationship with the date operation is two conversion functions: To_date (), To_char ()
The to_date () function converts a character type to a date type in a certain format :
Specific usage:

To_date (' 2004-11-27 ', ' yyyy-mm-dd ')

The former is a string, the latter is the conversion date format, note, before and after the two to a corresponding.
such as; To_date (' 2004-11-27 13:34:43 ', ' yyyy-mm-dd hh24:mi:ss ') will get specific time

Multiple date formats:

YYYY: Four-bit year yyy,yy,y: The last three, two, or one bit of the year, default to the current century MM:01~12 month number month: Nine characters in month, right with space to fill MON: three-character month abbreviation WW: Week of the Year D: Day of the week DD: Day of the month DDD: Days of the Year day: nine characters full name, right with space hh,hh12: Hours of the day, 12 notation HH24: The number of hours of the day, the value is 00. ~23

To_char (): Converts a date to a character type in a certain format-(used here for conditional queries)

Time
-------------------
2004-10-08 15:22:58

Converts the current time into a character type in YYYY-MM-DD hh24:mi:ss format

Working with date Daquan in Oracle

To_date format
Day:
DD Number 12
DY abbreviated Fri
Day spelled out Friday
Ddspth spelled out, ordinal twelfth
Month:
MM Number 03
Mon abbreviated Mar
Month spelled out March
Year:
YY digits 98
YYYY four digits 1998

The time range in the 24-hour format is: 0:00:00-23:59:59 ....
The time range in the 12-hour format is: 1:00:00-12:59:59 ....

Date--sql Query