Date formatting!

Just started looking at Reporting Services, I have created a report but
the date format I'm getting back is incorrect. I've seen something
that talks about .Net being responsible but I don't think I could code
anything to help. I'm not a programmer!
My statement returns the results as I would like in SQL, but clearly
doesn't apply to Reporting Services, as I'm getting an American format.
Code below for SQL which returns dd/mm/yy as I would like. How do I
got about getting this format into the report?
(invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
CONVERT(DATETIME, @.EndDate, 3))
Any help, gratefully received!
GaryIf you look at the rdl code for the report, and search for <Language>
you will find the language tag and notice that it has defaulted to
en-US. If you change it to en-GB you will get =A3 symbols and UK
formatted. If you still want to format the date further, you can use
FormatDateTime()
regards
weelin
On Oct 25, 12:43 pm, gdav...@.hotmail.com wrote:
> Just started looking at Reporting Services, I have created a report but
> the date format I'm getting back is incorrect. I've seen something
> that talks about .Net being responsible but I don't think I could code
> anything to help. I'm not a programmer!
> My statement returns the results as I would like in SQL, but clearly
> doesn't apply to Reporting Services, as I'm getting an American format.
> Code below for SQL which returns dd/mm/yy as I would like. How do I
> got about getting this format into the report?
> (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> CONVERT(DATETIME, @.EndDate, 3))
> > Any help, gratefully received!
> > Gary|||Hi,
The only safe solution I have found is to use "safe" format for
date-time as a string
yyyy-MM-dd (yyyy-MM-dd HH:mm:ss.mmm) and then to pass strings in
between reports.
CONVERT(VARCHAR(20),GETDATE(),120) -- from SQL
=Format(Now,"yyyy-MM-dd HH:mm:ss") -- inside reporting services
=Cdate("2006-10-25 14:15:00") -- string to date
This seems to work fine regardless of PC setups on the network.
The drawback is that you lose the date picker.
-- This one adds 16 hours to STRING parameter called TheDay
=format(DateAdd("h",16,cdate(Parameters!TheDay.Value)),"yyyy-MM-dd")
-- if you want to re-format string for display try
=Format(cdate("2006-10-25"),"dd/MM/yy") --October 25, 2005
I'm in Canada and working with British-American formats always ends up
with lots of errors and headache.
As a general rule I tend to use only two date formats whenever
possible:
1. 2006-10-25
2. October 25, 2006
Everything else is ambiguous.
Sincerely,
Damir
gdavid9@.hotmail.com wrote:
> Just started looking at Reporting Services, I have created a report but
> the date format I'm getting back is incorrect. I've seen something
> that talks about .Net being responsible but I don't think I could code
> anything to help. I'm not a programmer!
> My statement returns the results as I would like in SQL, but clearly
> doesn't apply to Reporting Services, as I'm getting an American format.
> Code below for SQL which returns dd/mm/yy as I would like. How do I
> got about getting this format into the report?
> (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> CONVERT(DATETIME, @.EndDate, 3))
> Any help, gratefully received!
> Gary|||Thanks for the replies guys, I eventually figured out that I needed MM
rather than mm. I do agree that using a date similar to 10 October
2006 would rule out any potential issues as this does seem to be
somewhat of a common problem.
Thanks
Gary
Damir wrote:
> Hi,
> The only safe solution I have found is to use "safe" format for
> date-time as a string
> yyyy-MM-dd (yyyy-MM-dd HH:mm:ss.mmm) and then to pass strings in
> between reports.
> CONVERT(VARCHAR(20),GETDATE(),120) -- from SQL
> =Format(Now,"yyyy-MM-dd HH:mm:ss") -- inside reporting services
> =Cdate("2006-10-25 14:15:00") -- string to date
> This seems to work fine regardless of PC setups on the network.
> The drawback is that you lose the date picker.
> -- This one adds 16 hours to STRING parameter called TheDay
> =format(DateAdd("h",16,cdate(Parameters!TheDay.Value)),"yyyy-MM-dd")
> -- if you want to re-format string for display try
> =Format(cdate("2006-10-25"),"dd/MM/yy") --October 25, 2005
> I'm in Canada and working with British-American formats always ends up
> with lots of errors and headache.
> As a general rule I tend to use only two date formats whenever
> possible:
> 1. 2006-10-25
> 2. October 25, 2006
> Everything else is ambiguous.
> Sincerely,
> Damir
> gdavid9@.hotmail.com wrote:
> > Just started looking at Reporting Services, I have created a report but
> > the date format I'm getting back is incorrect. I've seen something
> > that talks about .Net being responsible but I don't think I could code
> > anything to help. I'm not a programmer!
> >
> > My statement returns the results as I would like in SQL, but clearly
> > doesn't apply to Reporting Services, as I'm getting an American format.
> > Code below for SQL which returns dd/mm/yy as I would like. How do I
> > got about getting this format into the report?
> >
> > (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> > CONVERT(DATETIME, @.EndDate, 3))
> >
> > Any help, gratefully received!
> >
> > Gary