Different formats of DATE insertion

Bandi
Posted by Bandi under SQL Server category on | Points: 40 | Views : 870
Most of the applications need and manage date and time variables in their database. Since data from applications and external sources may come in many formats, you need to know how to insert these dispersant values into DATE, DATETIME and SMALLDATETIME columns.
I wish to provide you the different formats of DATE values for DATE type in SQL Server.
Here is a sample script that inserts the same date 10/27/1988 into a sample table using a number of different string formats
SET DATEFORMAT MDY
GO

CREATE TABLE X(D DATE)

INSERT INTO X VALUES ('19881027') --YYYYMMDD (ISO Format)
INSERT INTO X VALUES ('881027') --YYMMDD
INSERT INTO X VALUES ('10/27/1988') --MM/DD/YYYY
INSERT INTO X VALUES ('10/27/88') -- MM/DD/YY
INSERT INTO X VALUES ('27 OCT 1988') -- DD MON YYYY
INSERT INTO X VALUES ('27 OCT 88') -- DD MON YY
INSERT INTO X VALUES ('OCT 27 1988') -- MON DD YYYY
INSERT INTO X VALUES ('OCT 27, 1988') -- MON DD, YYYY
INSERT INTO X VALUES ('OCT 27, 88') -- MON DD, YY
INSERT INTO X VALUES ('OCTOBER 27, 1988') -- MONTH DD, YYYY
SELECT * FROM X

DROP TABLE X
/*OUTPUT:
D
1988-10-27
1988-10-27
1988-10-27
1988-10-27
1988-10-27
1988-10-27
1988-10-27
1988-10-27
1988-10-27
1988-10-27
*/


Note: Better to use ISO format for DATE types to have good DATE range comparisons and make sure that use proper data type in your application/database

Comments or Responses

Posted by: Jayakumars on: 9/3/2013 Level:Bronze | Status: [Member] [MVP] | Points: 10
HI
BANDI

GOOD POST

Login to post response