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")

Newbi

SSCertifiable

Points: 6546
More actions
January 12, 2017 at 6:44 am

#316914

Datetime format of Main Database was strutted incorrectly. It is displaying in mm/dd/yyyy. But I need it in ("dd-MMM-yyy") format to calculate some data out. How can I resolve it?
I suspect there is some changes wrong with my SSMS . Or should I have to change in my dataset query?
In fact, I have to use that correct format inside select query . How can I write it so that it will convert in dataset level. ?

Viewing 15 posts - 1 through 15 (of 21 total)

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

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

What data type is your field return as in your SQL? if you want to format it to look a certain way in SSRS, and it's coming back as a DATE or DATETIME then you can simply change the format of the field on your SSRS report. Eithe ryou can right click the field and in the number settings of it's properties, or key f4 on the field and change the format in the properties Pane.

Why do you need to ensure that it's a particular format in the Query if it's the presentation layer that you want it to be correct on?

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 2

Thom A - Thursday, January 12, 2017 7:12 AM
What data type is your field return as in your SQL? if you want to format it to look a certain way in SSRS, and it's coming back as a DATE or DATETIME then you can simply change the format of the field on your SSRS report. Eithe ryou can right click the field and in the number settings of it's properties, or key f4 on the field and change the format in the properties Pane.
Why do you need to ensure that it's a particular format in the Query if it's the presentation layer that you want it to be correct on?

It is datetime. It returns as 2011-08-23 00:00:00.000.I couldn't make changes in SQL because it is live production server. Thus why I need to change in presentation level of SSRS.

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

The fact that you stated "In fact, I have to use that correct format inside select query ." Suggested otherwise.

My answer above is your solution then. Change to format of the text field on your report to "dd-MMM-yyyy".

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 4

Thom A - Thursday, January 12, 2017 7:36 AM
The fact that you stated "In fact, I have to use that correct format inside select query ." Suggested otherwise.
My answer above is your solution then. Change to format of the text field on your report to "dd-MMM-yyyy".

I've changed it in Text Fields too, but it is only showing dd-MMM-yyyy instead of extracting the actual data. What should it be declared in Text Property?

Newbi SSCertifiable Points: 6546 More actions · Answer 5

Newbi - Thursday, January 12, 2017 7:41 AM
Thom A - Thursday, January 12, 2017 7:36 AM
The fact that you stated "In fact, I have to use that correct format inside select query ." Suggested otherwise.
My answer above is your solution then. Change to format of the text field on your report to "dd-MMM-yyyy".
I've changed it in Text Fields too, but it is only showing dd-MMM-yyyy instead of extracting the actual data. What should it be declared in Text Property?

And when I don't format it , it is showing wrong format. Any way to change it in SSRS?

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

Did you not read my initial reply?

I've quoted it below and bolded the relevant text.

Thom A - Thursday, January 12, 2017 7:12 AM
What data type is your field return as in your SQL? if you want to format it to look a certain way in SSRS, and it's coming back as a DATE or DATETIME then you can simply change the format of the field on your SSRS report. Either you can right click the field and in the number settings of it's properties, or key f4 on the field and change the format in the properties Pane.
Why do you need to ensure that it's a particular format in the Query if it's the presentation layer that you want it to be correct on?

Thom~

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

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

Newbi - Thursday, January 12, 2017 7:41 AM
Thom A - Thursday, January 12, 2017 7:36 AM
The fact that you stated "In fact, I have to use that correct format inside select query ." Suggested otherwise.
My answer above is your solution then. Change to format of the text field on your report to "dd-MMM-yyyy".
I've changed it in Text Fields too, but it is only showing dd-MMM-yyyy instead of extracting the actual data. What should it be declared in Text Property?

Sounds like you've changed the value of the field, not the format.

Your pane should look like this:

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 8

Thom A - Thursday, January 12, 2017 8:07 AM
Newbi - Thursday, January 12, 2017 7:41 AM
Thom A - Thursday, January 12, 2017 7:36 AM
The fact that you stated "In fact, I have to use that correct format inside select query ." Suggested otherwise.
My answer above is your solution then. Change to format of the text field on your report to "dd-MMM-yyyy".
I've changed it in Text Fields too, but it is only showing dd-MMM-yyyy instead of extracting the actual data. What should it be declared in Text Property?
Sounds like you've changed the value of the field, not the format.
Your pane should look like this:

yes, exactly. But If I change as above, It is only showing "dd-MMM-yyyy" instead of the actual data.

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

Something you're doing isn't correct then. When in the screen above does it show that the Sample has a value of 31-Jan-2000? I just tried formatting text that doesn't have a type of date, and that returns the value of the field.

You haven't encapsulated the format in quotations at all have you? If you were to put "dd-MMM-yyyy" it treats that as literal text, so your field would appear to value of "dd-MMM-yyyy". Much like if you have a field with the value with today's date, if you put the format to: dd"-mmm"-yyyy; Your field would instead have a value "12-mmm-2017"

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 10

Thom A - Thursday, January 12, 2017 9:27 AM
Something you're doing isn't correct then. When in the screen above does it show that the Sample has a value of 31-Jan-2000? I just tried formatting text that doesn't have a type of date, and that returns the value of the field.
You haven't encapsulated the format in quotations at all have you? If you were to put "dd-MMM-yyyy" it treats that as literal text, so your field would appear to value of "dd-MMM-yyyy". Much like if you have a field with the value with today's date, if you put the format to: dd"-mmm"-yyyy; Your field would instead have a value "12-mmm-2017"

I have encapsulated as =Format(Fields!DueDate.Value, "dd-MMM-yyyy") . May be that's why it is showing formatting data instead of actual data? But I thought it is the format for date time data type.

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

Newbi - Thursday, January 12, 2017 10:56 AM
Thom A - Thursday, January 12, 2017 9:27 AM
Something you're doing isn't correct then. When in the screen above does it show that the Sample has a value of 31-Jan-2000? I just tried formatting text that doesn't have a type of date, and that returns the value of the field.
You haven't encapsulated the format in quotations at all have you? If you were to put "dd-MMM-yyyy" it treats that as literal text, so your field would appear to value of "dd-MMM-yyyy". Much like if you have a field with the value with today's date, if you put the format to: dd"-mmm"-yyyy; Your field would instead have a value "12-mmm-2017"
I have encapsulated as =Format(Fields!DueDate.Value, "dd-MMM-yyyy") . May be that's why it is showing formatting data instead of actual data? But I thought it is the format for date time data type.

There's no need to use the format function in an expression. Simply have the value of the field the value of the field. Then change the format of the cell as I directed above.

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 12

Thom A - Thursday, January 12, 2017 1:29 PM
Newbi - Thursday, January 12, 2017 10:56 AM
Thom A - Thursday, January 12, 2017 9:27 AM
Something you're doing isn't correct then. When in the screen above does it show that the Sample has a value of 31-Jan-2000? I just tried formatting text that doesn't have a type of date, and that returns the value of the field.
You haven't encapsulated the format in quotations at all have you? If you were to put "dd-MMM-yyyy" it treats that as literal text, so your field would appear to value of "dd-MMM-yyyy". Much like if you have a field with the value with today's date, if you put the format to: dd"-mmm"-yyyy; Your field would instead have a value "12-mmm-2017"
I have encapsulated as =Format(Fields!DueDate.Value, "dd-MMM-yyyy") . May be that's why it is showing formatting data instead of actual data? But I thought it is the format for date time data type.
There's no need to use the format function in an expression. Simply have the value of the field the value of the field. Then change the format of the cell as I directed above.

This might be a lot easier if you handled the format on the SQL Server. You can create a custom column with the below.
SELECT replace(convert(varchar(11), getdate(), 113),' ','-')

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

Newbi SSCertifiable Points: 6546 More actions · Answer 13

MMartin1 - Thursday, January 12, 2017 4:02 PM
Thom A - Thursday, January 12, 2017 1:29 PM
Newbi - Thursday, January 12, 2017 10:56 AM
Thom A - Thursday, January 12, 2017 9:27 AM
Something you're doing isn't correct then. When in the screen above does it show that the Sample has a value of 31-Jan-2000? I just tried formatting text that doesn't have a type of date, and that returns the value of the field.
You haven't encapsulated the format in quotations at all have you? If you were to put "dd-MMM-yyyy" it treats that as literal text, so your field would appear to value of "dd-MMM-yyyy". Much like if you have a field with the value with today's date, if you put the format to: dd"-mmm"-yyyy; Your field would instead have a value "12-mmm-2017"
I have encapsulated as =Format(Fields!DueDate.Value, "dd-MMM-yyyy") . May be that's why it is showing formatting data instead of actual data? But I thought it is the format for date time data type.
There's no need to use the format function in an expression. Simply have the value of the field the value of the field. Then change the format of the cell as I directed above.
This might be a lot easier if you handled the format on the SQL Server. You can create a custom column with the below.
SELECT replace(convert(varchar(11), getdate(), 113),' ','-')

Does it have anything to do with language of your SQL ?

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

Not sure what you mean by "the language of your SQL". You mean if it is ANSI compliant? Is your data source something other than SQL Server?

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