Date formatting in SQL SERVER, and instructions for using GETDATE() and CONVERT() functions.
Date formatting in SQL SERVER
- 1. date and datetime Type difference
- 2. GETDATE () function, return the current date and time (datetime type)
- 3. CONVERT () function, format the date
1. The difference between date and datetime types
Date is the new data type introduced in SQL Server 2008. It represents a date, does not contain a time part, and can represent a date range from January 1, 1999 to December 31, 9999. Only 3 bytes of storage are required.
dateTime date and time part, can represent the date range from 00:00:00.000 on January 1, 1753 to 23:59:59.997 on December 31, 9999, accurate to 3.33 milliseconds, it requires 8 bytes of storage space.
GETDATE () function
return the current date and time (datetime type)
SELECT GETDATE();
return
2017-03-16 09:10:08.947
For example, create a form M
CREATE TABLE [dbo].[M](
[id] [int] NULL,
[date] [date] NULL,
[time] [datetime] NULL
) ON [PRIMARY]
You can insert data using the GETDATE() function
INSERT INTO M(id, date, time) VALUES (1, CONVERT(VARCHAR, GETDATE(), 110), GETDATE());
3. CONVERT () function, format the date
The CONVERT() function is a generic function that converts dates to new data types, and can also display date/time data in different formats.
grammar:
CONVERT(data_type(length),date,style)
- Data_type(length) specifies the target data type (with optional length). Date refers to the value that needs to be converted. Style Specifies the date/time output format.
- Style value that can be used: current time January 3, 2017 09:09:10:037
| Style ID | SQL | Corresponding format |
|---|---|---|
| 1 | SELECT CONVERT(varchar(100), GETDATE(), 1); | 01/03/17 |
| 2 | SELECT CONVERT(varchar(100), GETDATE(), 2); | 17.01.03 |
| 3 | SELECT CONVERT(varchar(100), GETDATE(), 3); | 03/01/17 |
| 4 | SELECT CONVERT(varchar(100), GETDATE(), 4); | 03.01.17 |
| 5 | SELECT CONVERT(varchar(100), GETDATE(), 5); | 03-01-17 |
| 6 | SELECT CONVERT(varchar(100), GETDATE(), 6); | 03 01 17 |
| 7 | SELECT CONVERT(varchar(100), GETDATE(), 7); | 01 03, 17 |
| 8 | SELECT CONVERT(varchar(100), GETDATE(), 8); | 09:09:10 |
| 9 | SELECT CONVERT(varchar(100), GETDATE(), 9); | 01 3 2017 9:09:10:037AM |
| 10 | SELECT CONVERT(varchar(100), GETDATE(), 10); | 01-03-17 |
| 11 | SELECT CONVERT(varchar(100), GETDATE(), 11); | 17/01/03 |
| 12 | SELECT CONVERT(varchar(100), GETDATE(), 12); | 170103 |
| 13 | SELECT CONVERT(varchar(100), GETDATE(), 13); | 03 01 2017 09:09:10:037 |
| 14 | SELECT CONVERT(varchar(100), GETDATE(), 14); | 09:09:10:037 |
| 20 | SELECT CONVERT(varchar(100), GETDATE(), 20); | 2017-01-03 09:09:10 |
| 21 | SELECT CONVERT(varchar(100), GETDATE(), 21); | 2017-01-03 09:09:10.037 |
| 22 | SELECT CONVERT(varchar(100), GETDATE(), 22); | 01/03/17 9:09:10 AM |
| 23 | SELECT CONVERT(varchar(100), GETDATE(), 23); | 2017-01-03 |
| 24 | SELECT CONVERT(varchar(100), GETDATE(), 24); | 09:09:10 |
| 25 | SELECT CONVERT(varchar(100), GETDATE(), 25); | 2017-01-03 09:09:10.037 |
| 100 | SELECT CONVERT(varchar(100), GETDATE(), 100); | 01 3 2017 9:09AM |
| 101 | SELECT CONVERT(varchar(100), GETDATE(), 101); | 01/03/2017 |
| 102 | SELECT CONVERT(varchar(100), GETDATE(), 102); | 2017.01.03 |
| 103 | SELECT CONVERT(varchar(100), GETDATE(), 103); | 03/01/2017 |
| 104 | SELECT CONVERT(varchar(100), GETDATE(), 104); | 03.01.2017 |
| 105 | SELECT CONVERT(varchar(100), GETDATE(), 105); | 03-01-2017 |
| 106 | SELECT CONVERT(varchar(100), GETDATE(), 106); | 03 01 2017 |
| 107 | SELECT CONVERT(varchar(100), GETDATE(), 107); | 01 03, 2017 |
| 108 | SELECT CONVERT(varchar(100), GETDATE(), 108); | 09:09:10 |
| 109 | SELECT CONVERT(varchar(100), GETDATE(), 109); | 01 3 2017 9:09:10:037AM |
| 110 | SELECT CONVERT(varchar(100), GETDATE(), 110); | 01-03-2017 |
| 111 | SELECT CONVERT(varchar(100), GETDATE(), 111); | 2017/01/03 |
| 112 | SELECT CONVERT(varchar(100), GETDATE(), 112); | 20170103 |
| 113 | SELECT CONVERT(varchar(100), GETDATE(), 113); | 03 01 2017 09:09:10:037 |
| 114 | SELECT CONVERT(varchar(100), GETDATE(), 114); | 09:09:10:037 |
| 120 | SELECT CONVERT(varchar(100), GETDATE(), 120); | 2017-01-03 09:09:10 |
| 121 | SELECT CONVERT(varchar(100), GETDATE(), 121); | 2017-01-03 09:09:10.037 |
| 126 | SELECT CONVERT(varchar(100), GETDATE(), 126); | 2017-01-03T09:09:10.037 |
| 130 | SELECT CONVERT(varchar(100), GETDATE(), 130); | 5 ???? ?????? 1438 9:09:10:037AM |
| 131 | SELECT CONVERT(varchar(100), GETDATE(), 131); | 5/04/1438 9:09:10:037AM |
Date formatting in SQL SERVER, and instructions for using GETDATE() and CONVERT() functions.