How can I convert, for example, ddmmyyyy which is a varchar(8) to date format(dd/mm/yyyy)?
I have a stored procedure which accepts a date varchar(8) parameter.
I want to convert it to date format before inserting into database. I tried to use
INSERT INTO mytable(createdDate) VALUES (CONVERT(date, CONVERT(varchar(8), @date), 106));
An error:
Conversion failed when converting date and/or time from character string.
createdDate column is type : date
ddmmyyyy is not a valid date format. You need to first make that string into something that can be parsed as a DATE / DATETIME. The quickest way might be to simply SUBSTRING the pieces into a mm/dd/yyyy format. That does convert successfully. But you have a VARCHAR(8). So you either need to increase that to be VARCHAR(10) (or better yet, just CHAR(10)), or declare a local variable to hold the altered value.
For example:
DECLARE @Date VARCHAR(8); -- input parameter
SET @Date = '25032014';
DECLARE @Date2 CHAR(10);
SET @Date2 = SUBSTRING(@Date, 3, 2)
+ '/' + SUBSTRING(@Date, 1, 2)
+ '/' + SUBSTRING(@Date, 5, 4);
SELECT @Date2, CONVERT(DATE, @Date2);
-- 03/25/2014 2014-03-25
EDIT:
Actually, I found a slightly simpler way. I started out with this method but realized that it did not work with ddmmyyyy as opposed to mmddyyyy. I somehow missed that there was an appropriate date style number for dd/mm/yyyy. So, simply adding two slashes to the incoming string and then calling CONVERT does work, but only if you use 103 as the "style". And like the first solution, it requires either changing the incoming parameter to be VARCHAR(10) or CHAR(10) instead of VARCHAR(8), or creating a local variable to be CHAR(10).
DECLARE @Date VARCHAR(8); -- input parameter
SET @Date = '25032014';
DECLARE @Date2 CHAR(10);
SET @Date2 = STUFF(STUFF(@Date, 3, 0, '/'), 6, 0, '/');
SELECT @Date2, CONVERT(DATE, @Date2, 103); -- 103 = dd/mm/yyyy
-- 25/03/2014 2014-03-25
Conversion "styles" can be found on the MSDN page for CAST and CONVERT.
