Convert to date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Convert to date

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-10-22 : 10:08:53

Hi

Can someone help with this?

In reporting services I have a text field that outputs as 20121018, the year the month and the date. Can I convert this to an expression in the the report table to output this as 18/10/2012?

Here is the current expression for the field:
=Fields!Solved_Date____CURRENT_DAY.Value

Thanks
S

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-22 : 11:26:16

Use an expression such as this:

=Mid(Cstr(20071018),7,2)&"/"&Mid(Cstr(20071018),5,2)&"/"&Mid(Cstr(20071018),1,4)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 12:31:04

FormatDateTime(CDate(Fields!yourtextfield.value))

provided you dont have spurious date values in your field

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-10-23 : 04:59:40

Getting error with:
=FormatDateTime(CDate(Fields!Solved_Date____CURRENT_DAY1.Value))

All the dates are originally formatted as 20121018, 20121019, 20121020...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-23 : 07:20:08

quote:
Originally posted by sz1

Getting error with:
=FormatDateTime(CDate(Fields!Solved_Date____CURRENT_DAY1.Value))

All the dates are originally formatted as 20121018, 20121019, 20121020...

CDate does not recognize the ISO format that you have. It processes dates based on the locale setting of your system.

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-10-23 : 07:43:23

Ok, is there another way?
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-23 : 07:46:47

Ugly and dirty as it may seem, the code I posted yesterday at 10/22/2012 : 11:26:16 should work.

Regarding the CDate approach, Visakh may have some suggestions - I was expressing my experience with CDate, but he may be able to offer more insight.

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-10-23 : 07:50:38

I will try as you suggested yesterday and let you know.
Thanks

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-10-24 : 06:20:20

Yes it works thanks a bunch.
Now got 00/00/00 format.
cool :)

With field below

=Mid(Cstr(Fields!Solved_Date____CURRENT_DAY.Value),7,2)&"/"&Mid(Cstr(Fields!Solved_Date____CURRENT_DAY.Value),5,2)&"/"&Mid(Cstr(Fields!Solved_Date____CURRENT_DAY.Value),1,4)

If the field is null I get //
Is there an IF statement we can add to the above
if null then '00/00/00'

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-24 : 06:59:22

Wrap your expression in a conditional like shown below. If the column is empty string rather than nulls, instead of "Is Nothing" use =""

IIF(Fields!Solved_Date____CURRENT_DAY.Value Is Nothing,
	"",
	Mid(Cstr(Fields!Solved_Date____CURRENT_DAY.Value),7,2)&"/"&Mid(Cstr(Fields!Solved_Date____CURRENT_DAY.Value),5,2)&"/"&Mid(Cstr(Fields!Solved_Date____CURRENT_DAY.Value),1,4)
)

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-10-24 : 07:07:41

Excellent!
Thanks again :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-24 : 07:35:40

Great! You are very welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources