1

How can I insert a date of format dd-mm-yyyy into a column of datatype Date?

Tried the below but showing errors

INSERT INTO Profiles (first_name, last_name, email, phone, 
                      [city(hometown)], created_at, gender, 
                      referral_code,marital_status) 
VALUES ('anusha', 'pariti', 'anusha.pariti@gmail.com', '8105987404', 
        'Bangalore', '16-04-2015', 'F', '7L5FZW', 'Y'),
       ('Ashish', 'Singh', 'ashish.singh@gmail.com', '9876890463', 
        'Bangalore','23-05-2015', 'M', 'KF34MF', 'Y')

Error:

Conversion failed when converting date and/or time from character string.

INSERT INTO Profiles (first_name, last_name, email, phone, 
                      [city(hometown)],created_at, gender, 
                      referral_code, marital_status) 
VALUES ('anusha', 'pariti', 'anusha.pariti@gmail.com', '8105987404', 
        'Bangalore', CAST('16-04-2015' AS DATE), 'F', '7L5FZW', 'Y'),
       ('Ashish', 'Singh', 'ashish.singh@gmail.com', '9876890463', 
        'Bangalore', CAST('23-05-2015' AS DATE), 'M', 'KF34MF', 'Y')

Error:

Conversion failed when converting date and/or time from character string.

INSERT INTO Profiles (first_name, last_name, email, phone, 
                      [city(hometown)], created_at, gender,  
                      referral_code, marital_status) 
VALUES ('anusha', 'pariti', 'anusha.pariti@gmail.com', '8105987404', 
        'Bangalore', CONVERT(DATE, '16-04-2015'), 'F', '7L5FZW', 'Y'),
       ('Ashish', 'Singh', 'ashish.singh@gmail.com', '9876890463', 
        'Bangalore', CONVERT(DATE, '23-05-2015'), 'M', 'KF34MF', 'Y')

Error:

Conversion failed when converting date and/or time from character string.

frederj
  • 1,483
  • 9
  • 20
Biswa
  • 343
  • 2
  • 4
  • 14

5 Answers5

2

Try inserting your dates using an acceptable format, e.g. use 20150416 instead of '16-04-2015:

INSERT INTO Profiles (first_name, last_name, email, phone, [city(hometown)],
    created_at, gender, referral_code,marital_status)
VALUES
    ('anusha', 'pariti', 'anusha.pariti@gmail.com', '8105987404', 'Bangalore',
     '20150416', 'F', '7L5FZW', 'Y'),
    ('Ashish', 'Singh', 'ashish.singh@gmail.com', '9876890463', 'Bangalore',
     '20150523', 'M', 'KF34MF', 'Y');

This will allow you store the data as dates inside the created_at column. This is absolutely the way to store your date information. If you later want to format the created_at column in your original way, you could use CONVERT, e.g.

SELECT
    CONVERT(varchar, created_at, 105) AS created_at
FROM Profiles;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Yes the acceptable format is working. But what if i want the format as specified i.e 16-04-2015. Is there a way to insert it in that format? – Biswa Sep 29 '17 at 04:21
  • 1
    Not that I know of. In any case, why would you even want to do that? The formats are wrong and you would have to do verbose conversions later on. – Tim Biegeleisen Sep 29 '17 at 04:24
  • @Biswa you can't insert the date ('16-04-2015') as per your need in datetime field, but later you can read as per your required format. – Yogesh Sharma Sep 29 '17 at 04:26
  • Ok. Thats what i wanted to know if it was possible in anyway. Thanks – Biswa Sep 29 '17 at 04:28
  • It is possible for date literals of YYYY-MM-DD to fail in MSSQL refer to https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries While it may be non-standard, and not intuitive, date literals of YYYYMMDD are always safe to use (in MSSQL!). – Paul Maxwell Sep 29 '17 at 06:10
1

The safest date literal in SQL Server is YYYYMMDD

E.g. INSERT into ... Values ('20170523', ...

It seems you are used to DD-MM-YYYY and if you want to use that sequence you then need to use convert with a style number.

convert(date, '23-05-2017',120)

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • It is bad advice to suggest inserting dates as text, despite that your conversion logic might work. – Tim Biegeleisen Sep 29 '17 at 04:32
  • Then you are attempting to insert an invalid date value like the 30th of February. The error message can be encountered simply because a string just will not become a date. – Paul Maxwell Sep 29 '17 at 04:56
  • By the way you MUST include the stule number when using CONVERT(date,'2015-05-23',120) if you dont include that third parameter you are relying on default settings which wont help you. Also not thst dates are stored as numbers, not in a human readable format. Formats matter at input and can be changed to suit output. – Paul Maxwell Sep 29 '17 at 05:02
  • @Tim_Biegeleisen In MSSQL 'YYYYMMDD" is actually safer than 'YYYY-MM-DD' because there is one (albeit very uncommon) default that can be set which would interpret the date as 'YYYY-DD-MM'. So it is bad advice to propose a 'YYYY-MM-DD' literal whereas 'YYYYMMDD' is always interpreted in that sequence. Personally I hate it, just happens to be the way it is. – Paul Maxwell Sep 29 '17 at 05:51
0

In SQL server 2012+ you can use

Select try_convert(date, '16-04-2015', 105)

otherwise you have to format the string correctly

dhiman
  • 385
  • 3
  • 9
0

Have you tried YYYY-MM-DD format? The data format in SQL is YYYY-MM-DD.

24x7servermanagement
  • 2,520
  • 1
  • 13
  • 11
0

You can try this.

dd-mm-yyyy is Italian format and style code is 105, that's why you should convert it by using this code.

INSERT INTO Profiles (first_name, last_name, email, phone, 
                      [city(hometown)], created_at, gender,  
                      referral_code, marital_status) 
VALUES ('anusha', 'pariti', 'anusha.pariti@gmail.com', '8105987404', 
        'Bangalore', CONVERT(DATE, '16-04-2015',105), 'F', '7L5FZW', 'Y'),
       ('Ashish', 'Singh', 'ashish.singh@gmail.com', '9876890463', 
        'Bangalore', CONVERT(DATE, '23-05-2015',105), 'M', 'KF34MF', 'Y') 
Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44