Displaying nvarchar datatype column having a date value

Question

Displaying nvarchar datatype column having a date value

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
September 30, 2013 at 9:58 pm

#298424

Please help me in displaying a nvarchar datatype column called 'txtValue' having date value in the format '15 Jul 2013' in my SSRS report

Viewing 9 posts - 1 through 8 (of 8 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Any reason you're storing a number (a date) in an NVARCHAR field? If you can fix that, then the problem would go away.

Did you try casting the value as a date in SSRS or creating a calculated value that does it? (since there are a lot of predefined formats that you can use if you do that)...

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 2

u can cast / convert the data type of column in your source query or Stored Procedures.

Well, why are u using nvarchar to store a date.? 🙂

BrainDonor SSCoach Points: 19254 More actions · Answer 3

All of the date manipulation bits you should ever need: https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

Steve Hall
Linkedin
Blog Site

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 4

Thanks everybody.

I fixed the issue, this way using SSRS report expression

Format(CDate(Fields!DateOfReportManagement.Value), "dd-MMM-yyyy")

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Great, now change the data type to the correct data type in the table. And if possible, change the column name to something much more descriptive. One should be able to look at the column name, and know exactly what is stored in that column.

Andrew SQLDBA

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

AndrewSQLDBA (10/1/2013)
Great, now change the data type to the correct data type in the table. And if possible, change the column name to something much more descriptive. One should be able to look at the column name, and know exactly what is stored in that column.

Andrew, you hit the nail on the head there. You know, I've seen more questions posted on handling dates stored in string fields that it makes my head spin. The data type ensures that the data is stored properly and is independent of format or presentation.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 7

Exactly Correct Ed

And it takes so much more time, code and processing power to convert all that string garbage into what you really want, if you only use the correct data types. Not only for dates, for everything. I Never use International data types, unless I am storing International characters, and that is Very rare that I design a database for that purpose. But yet I see people using nvarchar() All the time. That is a total waste of space unless it is truly needed.

Andrew SQLDBA

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

SELECT CAST(N'15 Jul 2013' AS DATE)

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St