How can I convert SQL correct date format in RDL("dd-MMM-yyyy")

Question

How can I convert SQL correct date format in RDL("dd-MMM-yyyy")

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Newbi SSCertifiable Points: 6546 More actions · Answer 1

What I mean is 'The SQL language in my machine is US-english after I check with select @@languange

Thom A SSC Guru Points: 99130 More actions · Answer 2

You're SQL language won't make any difference to how format works, no. You define the format using characters, for example dd = two character day, MMM = three character month.

Have you tried not using the format function and done as I suggested above?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Newbi SSCertifiable Points: 6546 More actions · Answer 3

Thom A - Friday, January 13, 2017 1:45 AM
You're SQL language won't make any difference to how format works, no. You define the format using characters, for example dd = two character day, MMM = three character month.
Have you tried not using the format function and done as I suggested above?

I see... yet it does affect when doing calculation right?
I have formatted as "dd-MMM-yyyy" but it get no formatting on the data. and instead it is displaying dd-MMM-yyy.

Thom A SSC Guru Points: 99130 More actions · Answer 4

Newbi - Friday, January 13, 2017 2:00 AM
Thom A - Friday, January 13, 2017 1:45 AM
You're SQL language won't make any difference to how format works, no. You define the format using characters, for example dd = two character day, MMM = three character month.
Have you tried not using the format function and done as I suggested above?
I see... yet it does affect when doing calculation right?
I have formatted as "dd-MMM-yyyy" but it get no formatting on the data. and instead it is displaying dd-MMM-yyy.

Could you post your rdl?

Also, how do these field look your end? (You'll need to add a Datasource)

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Newbi SSCertifiable Points: 6546 More actions · Answer 5

The culprit is some datatype conversion inside my query.
Thanks Thom, for trying to helping me out. Actually, I was having some difficulty in extracting out the data from Server/Or mimic the dataset.
After I removed unnecessary data converting from my query , the due date data is shown correctly with any formatting.

Thom A SSC Guru Points: 99130 More actions · Answer 6

Good to hear you got it working.

If you're going to be changing the way you present your data in the presentation layer, always return the data in it's original data type (dates as DATE/DATETIME, Numerics as INTEGER/DECIMAL). Don't convert them to strings which represent your data, or other data types. It makes manipulating them at the presentation layer that much harder. For example, returning a monetary field with the values "£1,000.00" and "£2,000.00" is going to cause your presentation layer to have no idea how to sum it, as would see the £ and probably the , as special characters that do not belong in a number.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

Newbi - Friday, January 13, 2017 3:27 AM
The culprit is some datatype conversion inside my query.
Thanks Thom, for trying to helping me out. Actually, I was having some difficulty in extracting out the data from Server/Or mimic the dataset.
After I removed unnecessary data converting from my query , the due date data is shown correctly with any formatting.

I was going to ask you to post your actual SQL code that the report calls. Just to not neglect what is happening underneath the hood. Good to hear you have this working properly.

----------------------------------------------------