Need Help with TSQL for Date Display

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Need Help with TSQL for Date Display

Need Help with TSQL for Date Display

Rate Topic

Display Mode

Topic Options

Author

Message

dhananjay.nagarkar

Posted Thursday, March 21, 2013 2:34 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 9:07 AM
Points: 43, Visits: 80

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

Post #1434028

Evil Kraig F

Posted Thursday, March 21, 2013 2:52 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 5,722, Visits: 6,194

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

Post #1434038

dhananjay.nagarkar

Posted Thursday, March 21, 2013 3:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 9:07 AM
Points: 43, Visits: 80

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

Post #1434058

Evil Kraig F

Posted Thursday, March 21, 2013 3:52 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 5,722, Visits: 6,194

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.

Post #1434065

dhananjay.nagarkar

Posted Thursday, March 21, 2013 4:09 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 9:07 AM
Points: 43, Visits: 80

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

Post #1434067

Evil Kraig F

Posted Thursday, March 21, 2013 5:25 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 5,722, Visits: 6,194

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)

Post #1434085

dhananjay.nagarkar

Posted Friday, March 22, 2013 3:54 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 9:07 AM
Points: 43, Visits: 80

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

Post #1434179

dhananjay.nagarkar

Posted Friday, March 22, 2013 4:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 9:07 AM
Points: 43, Visits: 80

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

Post #1434180

« Prev Topic | Next Topic »

Permissions