Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Help with TSQL for Date Display Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 2:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
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
Posted Thursday, March 21, 2013 2:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Posted Thursday, March 21, 2013 3:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
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
Posted Thursday, March 21, 2013 3:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Post #1434065
Posted Thursday, March 21, 2013 4:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
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
Posted Thursday, March 21, 2013 5:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Post #1434085
Posted Friday, March 22, 2013 3:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
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
Posted Friday, March 22, 2013 4:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
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 »

Add to briefcase

Permissions Expand / Collapse