CODEWITHSUNDEEP
sqlsql-servert-sql
Griemmi
Griemmi

Reputation: 11

datetime argument data type varchar is invalid for argument 3 of convert function

I am trying to populate a table in my database with the following:

INSERT INTO student 
VALUES (102, 'Mr.', 'Fred', 'Crocitto', '101-09 120th St.', '11419', '718-555-5555', 
        'Albert Hildegard Co.', '22-JAN-03', 'BROSENZWEIG',
        CONVERT(DATETIME, '19-JAN-2007 00:00: 00','DD-MON-YYYY HH24:MI:SS'),
        'BROSENZW', CONVERT(DATETIME, '22-JAN-2007 00:00:00', 'DD-MON-YYYY HH24:MI:SS'));

When I execute it, I get this error message:

Msg 8116, Level 16, State 1, Line 2
Argument data type varchar is invalid for argument 3 of convert function

What am I doing wrong?

Upvotes: 1

Views: 16052

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 176024

You can use CONVERT with style 113 or just place string date literal:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

CREATE TABLE #student(col DATETIME /*,rest_of_columns */);

INSERT INTO #student 
VALUES (CONVERT(DATETIME, '19-JAN-2007 00:00:00',113) /* ,rest_of_columns */);

INSERT INTO #student              -- style 113 is default so you can skip convert
VALUES ('19-JAN-2007 00:00:00');

SELECT *
FROM #student;

LiveDemo

EDIT:

Datetime accepts style.

@Brian Stork

When you convert TO datetime, you can't add a style. Datetime does not accept style. You can convert datetime to character with a style, but not the other way

According to official CONVERT documentation (section remarks):

╔══════════════════════════╦═════════════════════╦══════════╦══════════════════╗
║ Without century (yy) (1) ║ With century (yyyy) ║ Standard ║ Input/Output (3) ║
╚══════════════════════════╩═════════════════════╩══════════╩══════════════════╝

3 Input when you convert to datetime; output when you convert to character data.

Plus consider example like:

CREATE TABLE #tab(col DATETIME);

INSERT INTO #tab
VALUES ('12.02.02')
       ,(CONVERT(DATETIME, '12.02.02', 2));

SELECT *
FROM #tab;

LiveDemo2

Result:

╔═════════════════════╗
║         col         ║
╠═════════════════════╣
║ 2002-12-02 00:00:00 ║
║ 2012-02-02 00:00:00 ║
╚═════════════════════╝

Upvotes: 2

Brian Stork
Brian Stork

Reputation: 980

Datetime does not accept a style. If you want to convert it to a string, use the convert(char(length),'datevale','style') but if your column is type DATETIME, you don't need to add a style.

Don't use convert at all. Just use the string '19-JAN-2007 00:00:00'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

"What am I doing wrong?" You are using Oracle/Postgres arguments for a SQL Server function (confusing to_char() with convert()).

The easiest solution is to use a string that represents the datetime using ISO standard formats:

INSERT INTO student 
    VALUES (102,'Mr.','Fred','Crocitto','101-09 120th St.','11419','718-555-5555',
            'Albert Hildegard Co.','22-JAN-03','BROSENZWEIG',
            '2007-01-19', 'BROSENZW', '2007-01-22');

Upvotes: 0

Related Questions