March 21, 2013 at 2:34 pm
Dear Friends,
Currently from the DB table I get the Date and Time in DATE TIME STAMP format
1/2/2013 12:00:00 AM
But I want to display it without the TimeStamp i.e. 1/2/2013
Kind Regards
Dhananjay
March 21, 2013 at 2:52 pm
A) What is the base datatype for the field? I hope DATETIME.
B) Why? The front end/reporting server can do this so much easier, and end users shouldn't be running ad-hoc code so they should have an interface... somewhere. Even with CSV exports they load in excel and format the column.
C) The only way to do what you want is to turn the datetime into a VARCHAR() for display purposes. You will lose all date functionality, is that what you want?
After answering those, there is a way. It works off the style components in CONVERT()
SELECT
CONVERT( VARCHAR(10), <yourdate>, 101) AS NoLongerDateTimeField
There is no simple way within SQL however to trim the leading zeroes. You'll have to work with constructing the string directly using the MONTH DAY and YEAR functions and concatonating your results.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 21, 2013 at 3:31 pm
Hi Craig,
since I do not want to lose the date in the Report hence seems the best suggestion is in DB to do this change instead of DATE TIME STAMP only DATE?
right?
thanks
Dhananjay
March 21, 2013 at 3:52 pm
dhananjay.nagarkar (3/21/2013)
Hi Craig,since I do not want to lose the date in the Report hence seems the best suggestion is in DB to do this change instead of DATE TIME STAMP only DATE?
right?
You mention report. What's the report front end? It's better to format there.
The datatype is best selected based on need, not display requirements. If some places need the timecomponents, then you need DATETIME. If you never need time in a field, DATE is best for storage, yes.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 21, 2013 at 4:10 pm
Hi Craig,
I use SSRS 2008 visual studio where in i invoke my SP ..
The report is then download in excel ..by user
Thanks
DJ
March 21, 2013 at 5:25 pm
dhananjay.nagarkar (3/21/2013)
Hi Craig,I use SSRS 2008 visual studio where in i invoke my SP ..
The report is then download in excel ..by user
Thanks
DJ
In SSRS, go to the display box for your date. In properties, go to Format (near the bottom, usually). Hit the dropdown, choose expression builder. You'll want to use
=FormatDateTime(Fields!<yourfield>.Value, dateformat.ShortDate)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 22, 2013 at 3:54 am
Hi Eric,
The suggestion of usinf FORMAT tried but somehow when I used FORMAT for the field value then the field lost value, hence what I did is right clicked the field and then in the "Text Box" proprerties i saw 'Number' I chnaged that to Date field mm/dd/yyy.
that seem to have worked for me.
Kind Regards
Dhananjay
March 22, 2013 at 4:00 am
Hi Craig,
The FORMAT in SSRS did not work - I lost the value .
But here's what I did I right clicked the field , then in the Text Box Properties there is a Option called 'Number' clicked that it gave me sever options like Escel sheet gives and I selected Daate : mm/dd/yyyy.
Seems this is working.
Thanks again for helping and guiding me.
Kind Regards
Dhananjay
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply