CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Luv
Luv

Reputation: 44

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

I was trying to create a procedure as follows :

CREATE PROC prc_invoice_add
AS
BEGIN
insert into INVOICE
(INV_NUMBER,CUS_CODE,INV_DATE)
VALUES (8006,1000,cast('30-APRL-08 00:00:00 AM ' as DATETIME2))
END

But whenever I execute this procedure this error message pops up :

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

Upvotes: 1

Views: 997

Answers (3)

Aditya
Aditya

Reputation: 2311

Use convert, Specifically to cast date time.

CONVERT(DATETIME2,'30-APR-08 00:00:00 AM',113)

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

The problem is in Aprl

Pass it as April or Apr

try this

CREATE PROC prc_invoice_add
AS
BEGIN
insert into INVOICE
(INV_NUMBER,CUS_CODE,INV_DATE)
VALUES (8006,1000,cast('30-APRIL-08 00:00:00 AM ' as DATETIME2))
END

Upvotes: 1

Vikas Rana
Vikas Rana

Reputation: 1999

try this,

CREATE PROC prc_invoice_add
AS
BEGIN
insert into INVOICE
(INV_NUMBER,CUS_CODE,INV_DATE)
VALUES (8006,1000,cast('30-APR-08 00:00:00 AM ' as DATETIME2))
END

your format of date is dd-MMM-yyyy, in this format APRIL is writtan as apr.

Upvotes: 2

Related Questions