CODEWITHSUNDEEP
sqlsql-serversql-insertstring-to-datetime
Biswa
Biswa

Reputation: 397

Inserting a date in a column of date datatype in SQL Server

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', '[email protected]', '8105987404', 
        'Bangalore', '16-04-2015', 'F', '7L5FZW', 'Y'),
       ('Ashish', 'Singh', '[email protected]', '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', '[email protected]', '8105987404', 
        'Bangalore', CAST('16-04-2015' AS DATE), 'F', '7L5FZW', 'Y'),
       ('Ashish', 'Singh', '[email protected]', '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', '[email protected]', '8105987404', 
        'Bangalore', CONVERT(DATE, '16-04-2015'), 'F', '7L5FZW', 'Y'),
       ('Ashish', 'Singh', '[email protected]', '9876890463', 
        'Bangalore', CONVERT(DATE, '23-05-2015'), 'M', 'KF34MF', 'Y')

Error:

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

Upvotes: 1

Views: 12334

Answers (5)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

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', '[email protected]', '8105987404', 'Bangalore',
     '20150416', 'F', '7L5FZW', 'Y'),
    ('Ashish', 'Singh', '[email protected]', '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;

Upvotes: 2

Serkan Arslan
Serkan Arslan

Reputation: 13393

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', '[email protected]', '8105987404', 
        'Bangalore', CONVERT(DATE, '16-04-2015',105), 'F', '7L5FZW', 'Y'),
       ('Ashish', 'Singh', '[email protected]', '9876890463', 
        'Bangalore', CONVERT(DATE, '23-05-2015',105), 'M', 'KF34MF', 'Y')

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35553

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)

Upvotes: 1

24x7servermanagement
24x7servermanagement

Reputation: 2540

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

Upvotes: 0

dhiman
dhiman

Reputation: 517

In SQL server 2012+ you can use

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

otherwise you have to format the string correctly

Upvotes: 0

Related Questions