Convert DD/MM/YYYY to YYYY-MM-DD at SQL server 2008 R2

Question

I am using SSRS 2008, one report have text fields which takes data as DD/MM/YYYY as input, but at query i would like it to covert as YYYY-MM-DD.

I have tried with below code but could not succeed.

Declare @a varchar(20)= '27/03/2014'
SELECT CONVERT(varchar(20), CAST(@a as DATE),126)

Output required as

2014-03-27

try to check this out: http://stackoverflow.com/questions/2805512/convert-date-in-text-format-to-datetime-format-in-t-sql — Yohanes Khosiawan 许先汉, Mar 27 '14 at 04:19
it failed i have just tried Declare @b char(8)= '27032014' select CONVERT(datetime,RIGHT(@b,4) + left(@b,4)) and it resulted as "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." @Yohanes Khosiawan — Saifuddin, Mar 27 '14 at 04:36
try to check this out: http://stackoverflow.com/questions/5466100/conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-out-of — Yohanes Khosiawan 许先汉, Mar 27 '14 at 04:40
its a same as available in other threads.. cast and convert functions. my problem is input data at text field is like 27/04/2014.. when it come down as parameter to query, i need it to convert to yyyy-mm-dd. @Yohanes Khosiawan — Saifuddin, Mar 27 '14 at 04:47
actually you can eliminate that `/` character.. and you can use that `set dateformat dmy` to avoid the `out-of-range value` error — Yohanes Khosiawan 许先汉, Mar 27 '14 at 04:56
@Yohanes Khosiawan .. it worked as Set dateformat dmy Declare @a varchar(10)= '27/03/2014' SELECT CONVERT(varchar(10), CAST('27/03/2014' AS DATE),126) thanks a lot dude — Saifuddin, Mar 27 '14 at 07:36

score 0 · Accepted Answer · edited Mar 28 '14 at 04:42

In SSRS Report Date format Change use for this concept.

create table az(id integer,Dob varchar(20));

insert into az values(1, '25/03/2014')
insert into az values(2, '26/03/2014')
insert into az values(3, '27/03/2014')
insert into az values(4, '28/03/2014')
select * from az

Use this Line for your Textbox Field Expression:

=IIF(IsNothing(Fields!Dob.Value),"",Mid(Fields!Dob.Value,7,4) + "-" + 
Mid(Fields!Dob.Value,4,2) + "-" + Left(Fields!Dob.Value,2))

score 0 · Answer 2 · answered Jan 10 '15 at 03:17

DECLARE @DATECOL VARCHAR(13) = '25/01/2015'

-- Date Format
SELECT CAST(RIGHT(@DATECOL,4) + '-' + LEFT(RIGHT(@DATECOL,7),2) + '-' + LEFT(@DATECOL,2) AS DATE)

-- Varchar format
SELECT RIGHT(@DATECOL,4) + '-' + LEFT(RIGHT(@DATECOL,7),2) + '-' + LEFT(@DATECOL,2)

SQL FIDDLE

Convert DD/MM/YYYY to YYYY-MM-DD at SQL server 2008 R2

2 Answers2