Oracle and sqlserver date

1. In oracle in English, the default date format is 'dd-MON-YY ', For example, '01-Jan-98'

In the Chinese version of the Chinese version, the default Oracle date format is 'day-month-Year', for example, '21-January-2003-'or '21-January-03'

2.Convert string to date (to_date)

To_date (string_value, date_format)

String_value: Return Value of a string, a string column (a column of a table defined in the database), or a string internal function.

Date_format is a valid Oracle date format.

Example: to_date ('2014/1/22 12:04:01 ', 'yyyy/MM/DD hh24: MI: ss ')

3.Convert date to string(To_char)

To_char (date_value, date_format)

The variables are defined as follows: date_value: Date-type direct value (date itself) and date-type column value (a column of a table defined in the database) or the date type value returned by an internal function. Date_format is a valid Oracle date format.

Select the time section of the lessondate field:

Select (to_char (lessondate, 'hh24: MI: ss') lessondate from student;

 

 

SQL Server common date format conversion

The default datetime format of the Chinese version of SQL Server is yyyy-mm-dd thh: mm: Ss. Mmm. For example: Select getdate () 11:06:08. 177 I sorted out the date format conversion methods that may be frequently used in SQL Server: Example: Getdate: The time field of the table to be queried. Code Select convert (varchar, getdate (), 120 ) 2004 - 09 - 12   11 : 06 : 08 select Replace (replace (convert (varchar, getdate (), 120 ), ' - ' , '' ), ' ' , '' ), ' : ' , '' ) 20040912110608 Select convert (varchar (12), Getdate (),111) 2004/09/12  Select convert (varchar (12), Getdate (),112) 20040912  Select convert (varchar (12), Getdate (),102) 2004.09.12  Select convert (varchar (12), Getdate (),101) 09/12/2004  Select convert (varchar (12), Getdate (),103) 12/09/2004  Select convert (varchar (12), Getdate (),104) 12.09.2004  Select convert (varchar (12), Getdate (),105) 12-09-2004  Select convert (varchar (12), Getdate (),106) 12 09 2004  Select convert (varchar (12), Getdate (),107) 09 12,2004  Select convert (varchar (12), Getdate (),108) 11:06:08  select convert (varchar ( 12 ), getdate (), 109 ) 09 12 2004 1 select convert (varchar ( 12 ), getdate (), 110 ) 09 - 12 - 2004 select convert (varchar ( 12 ), getdate (), 113 ) 12 09 2004 1 Select convert (varchar (12), Getdate (),114) 11:06:08.177  Information in the help document Without Century (yy) With Century (YYYY) Standard Input/Output ** - 0 or 100 (*) Default Mon dd yyyy hh: miam (or pm) 1 101 USA Mm/DD/yy 2 102 ANSI YY. Mm. dd 3 103 British/French Dd/mm/yy 4 104 German Dd. mm. yy 5 105 Italian DD-mm-yy 6 106 - Dd mon YY 7 107 - Mon DD, YY 8 108 - Hh: mm: SS - 9 or 109 (*) Default + milliseconds Mon dd yyyy hh: MI: SS: mmmam (or pm) 10 110 USA Mm-dd-yy 11 111 Japan YY/MM/dd 12 112 ISO Yymmdd - 13 or 113 (*) Europe default + milliseconds Dd mon yyyy hh: mm: SS: Mmm (24 h) 14 114 - Hh: MI: SS: Mmm (24 h) - 20 or 120 (*) ODBC canonical Yyyy-mm-dd hh: MI: SS (24 h) - 21 or 121 (*) ODBC canonical (with milliseconds) Yyyy-mm-dd hh: MI: Ss. Mmm (24 h) - 126 (***) Iso8601 Yyyy-mm-dd thh: mm: SS: Mmm (no spaces) - 130 * Kuwaiti Dd mon yyyy hh: MI: SS: mmmam - 131 * Kuwaiti Dd/mm/yy hh: MI: SS: mmmam