SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help with TSQL for Date Display


Need Help with TSQL for Date Display

Author
Message
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 185
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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20567 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 185
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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20567 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 185
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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20567 Visits: 7660
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)


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 185
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
dhananjay.nagarkar
dhananjay.nagarkar
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 185
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search