Usage of CONVERT () date Conversion Function in SQLServer

First, we will see an introduction to the convert function on the official website, that is, the CONVERT () function converts a date to a common function of the new data type and CONVERT () functions can display date and time data in different formats.

First, we will see an introduction to the convert function on the official website, that is, the CONVERT () function converts a date to a common function of the new data type and CONVERT () functions can display date/time data in different formats.

Let's look at the vocabulary method first

The Code is as follows:
CONVERT (data_type (length), data_to_be_converted, style)

Example 1

The Code is as follows:
Select convert (varchar (30), getdate (), 101) now

Result:

Now
---------------------------------------
| 03/8/2013

Example 2

The Code is as follows:
1> select CONVERT (varchar (8), getdate (), 112) 2> go 20130308 (1 row affected) 1> select CONVERT (varchar (10), getdate (), 105) 2> go 08-03-2013 (1 row affected) 1> select getdate () 2> go 2013-03-08 10:30:30 (1 row affected) 1>

Obtain all data of a day

The Code is as follows:
1> select * from s_sign_log 2> go Id user_id room_id create_time 1 297211672 8586 16:05:52 2 297211672 8588 16:17:18 3 297211671 8588 16:25:14 4 297211671 9983 18:21:55 5 297211671 9983 10:33:17 (5 rows affected)

Call up yesterday's data

The Code is as follows:
1> select * from s_sign_log where CONVERT (varchar (8), create_time, 112) = CONVERT (varchar (8), dateadd (dd,-1, getdate (), 112) 2> go Id user_id room_id create_time 1 297211672 8586 16:05:52 2 297211672 8588 16:17:18 3 297211671 8588 16:25:14 4 297211671 9983 18:21:55 (4 rows affected)

Statement and query result:

The Code is as follows:

Select convert (varchar (100), GETDATE (), 0): 05 16 2006 AM Select convert (varchar (100), GETDATE (), 1): 05/16/06 Select convert (varchar (100), GETDATE (), 2): 06.05.16 Select convert (varchar (100), GETDATE (), 3): 16/05/06 Select convert (varchar (100), GETDATE (), 4): 16.05.06 Select convert (varchar (100), GETDATE (), 5): 16-05-06 Select convert (varchar (100), GETDATE (), 6): 16 05 06 Select convert (varchar (100), GETDATE (), 7): 05 16, 06 Select convert (varchar (100), GETDATE (), 8): 10:57:46 Select convert (varchar (100), GETDATE (), 9): 05 16 2006 10: 57: 46: 827AM Select convert (varchar (100), GETDATE (), 10): 05-16-06 Select convert (varchar (100), GETDATE (), 11): 06/05/16 Select convert (varchar (100), GETDATE (), 12): 060516 Select convert (varchar (100), GETDATE (), 13): 16 05 2006 10: 57: 46: 937 Select convert (varchar (100), GETDATE (), 14): 10: 57: 46: 967 Select convert (varchar (100), GETDATE (), 20): 10:57:47 Select convert (varchar (100), GETDATE (), 21): 10:57:47. 157 Select convert (varchar (100), GETDATE (), 22): 05/16/06 10:57:47 AM Select convert (varchar (100), GETDATE (), 23 ): Select convert (varchar (100), GETDATE (), 24): 10:57:47 Select convert (varchar (100), GETDATE (), 25): 10:57:47. 250 Select convert (varchar (100), GETDATE (), 100): 05 16 2006 AM Select convert (varchar (100), GETDATE (), 101): 05/16/2006 Select convert (varchar (100), GETDATE (), 102): 2006.05.16 Select convert (varchar (100), GETDATE (), 103): 16/05/2006 Select convert (varchar (100), GETDATE (), 104): 16.05.2006 Select convert (varchar (100), GETDATE (), 105): 16-05-2006 Select convert (varchar (100), GETDATE (), 106): 16 05 2006 Select convert (varchar (100), GETDATE (), 107): 05 16,200 6 Select convert (varchar (100), GETDATE (), 108): 10:57:49 Select convert (varchar (100), GETDATE (), 109): 05 16 2006 10: 57: 49: 437AM Select convert (varchar (100), GETDATE (), 110): 05-16-2006 Select convert (varchar (100), GETDATE (), 111): 2006/05/16 Select convert (varchar (100), GETDATE (), 112): 20060516 Select convert (varchar (100), GETDATE (), 113): 16 05 2006 10: 57: 49: 513 Select convert (varchar (100), GETDATE (), 114): 10: 57: 49: 547 Select convert (varchar (100), GETDATE (), 120): 10:57:49 Select convert (varchar (100), GETDATE (), 121): 2006