Date formatting in SQL SERVER, using 2 functions

Date formatting in SQL SERVER, and instructions for using GETDATE() and CONVERT() functions.

Date formatting in SQL SERVER
Date formatting in SQL SERVER

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 IDSQLCorresponding format
1SELECT CONVERT(varchar(100), GETDATE(), 1);01/03/17
2SELECT CONVERT(varchar(100), GETDATE(), 2);17.01.03
3SELECT CONVERT(varchar(100), GETDATE(), 3);03/01/17
4SELECT CONVERT(varchar(100), GETDATE(), 4);03.01.17
5SELECT CONVERT(varchar(100), GETDATE(), 5);03-01-17
6SELECT CONVERT(varchar(100), GETDATE(), 6);03 01 17
7SELECT CONVERT(varchar(100), GETDATE(), 7);01 03, 17
8SELECT CONVERT(varchar(100), GETDATE(), 8);09:09:10
9SELECT CONVERT(varchar(100), GETDATE(), 9);01 3 2017 9:09:10:037AM
10SELECT CONVERT(varchar(100), GETDATE(), 10);01-03-17
11SELECT CONVERT(varchar(100), GETDATE(), 11);17/01/03
12SELECT CONVERT(varchar(100), GETDATE(), 12);170103
13SELECT CONVERT(varchar(100), GETDATE(), 13);03 01 2017 09:09:10:037
14SELECT CONVERT(varchar(100), GETDATE(), 14);09:09:10:037
20SELECT CONVERT(varchar(100), GETDATE(), 20);2017-01-03 09:09:10
21SELECT CONVERT(varchar(100), GETDATE(), 21);2017-01-03 09:09:10.037
22SELECT CONVERT(varchar(100), GETDATE(), 22);01/03/17 9:09:10 AM
23SELECT CONVERT(varchar(100), GETDATE(), 23);2017-01-03
24SELECT CONVERT(varchar(100), GETDATE(), 24);09:09:10
25SELECT CONVERT(varchar(100), GETDATE(), 25);2017-01-03 09:09:10.037
100SELECT CONVERT(varchar(100), GETDATE(), 100);01 3 2017 9:09AM
101SELECT CONVERT(varchar(100), GETDATE(), 101);01/03/2017
102SELECT CONVERT(varchar(100), GETDATE(), 102);2017.01.03
103SELECT CONVERT(varchar(100), GETDATE(), 103);03/01/2017
104SELECT CONVERT(varchar(100), GETDATE(), 104);03.01.2017
105SELECT CONVERT(varchar(100), GETDATE(), 105);03-01-2017
106SELECT CONVERT(varchar(100), GETDATE(), 106);03 01 2017
107SELECT CONVERT(varchar(100), GETDATE(), 107);01 03, 2017
108SELECT CONVERT(varchar(100), GETDATE(), 108);09:09:10
109SELECT CONVERT(varchar(100), GETDATE(), 109);01 3 2017 9:09:10:037AM
110SELECT CONVERT(varchar(100), GETDATE(), 110);01-03-2017
111SELECT CONVERT(varchar(100), GETDATE(), 111);2017/01/03
112SELECT CONVERT(varchar(100), GETDATE(), 112);20170103
113SELECT CONVERT(varchar(100), GETDATE(), 113);03 01 2017 09:09:10:037
114SELECT CONVERT(varchar(100), GETDATE(), 114);09:09:10:037
120SELECT CONVERT(varchar(100), GETDATE(), 120);2017-01-03 09:09:10
121SELECT CONVERT(varchar(100), GETDATE(), 121);2017-01-03 09:09:10.037
126SELECT CONVERT(varchar(100), GETDATE(), 126);2017-01-03T09:09:10.037
130SELECT CONVERT(varchar(100), GETDATE(), 130);5 ???? ?????? 1438 9:09:10:037AM
131SELECT 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.

本站最新优惠

Namesilo优惠:新用户省 $1 域名注册-优惠码:45D%UYTcxYuCloZ 国外最便宜域名!点击了解更多

特别优惠:免费赠送 $100 Vultr主机-限时优惠!英文站必备海外服务器!点击了解更多

VPS优惠:搬瓦工优惠码:BWH3OGRI2BMW 最高省5.83%打开外面世界的一款主机点击了解更多

原创文章,作者:江小编,如若转载,请注明出处:https://jhrs.com/2018/25663.html

扫码关注【江湖人士】公众号,您会获得关于国外被动收入的最新资讯

WA付费会员QQ群:387027533,加这个群需要回答您的WA会员名,待核实后予以通过

普通QQ交流群:178758794,可分享交流建站的各类经验和知识

发表评论

电子邮件地址不会被公开。 必填项已用*标注

3 × 2 =