Format the column data for reader easilbilty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Format the column data for reader easilbilty

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-09-01 : 11:16:55

Hi,
I am having data in a table with 8 columns.
One of the column is date . its values are:-
2011-02-01 00:00:00.000,
2011-03-01 00:00:00.000...

It looks as if the date is feb 1st 2011 and march 1st 2011....BUT I took data from a underlying table where all the products sell(for all feb days ie feb 2011) are put up under 2011-02-01 00:00:00.000...

So, I dont want my users ( reading report) to get into an impression like the sales are for just 1 day as ACTUALLY its all feb month sales...
So i want date values as 2011-02 or 2011-Feb....so that they can know these sold are in whole month.

Thanks.

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-09-01 : 13:30:00

Ya i got it

select CONVERT(VARCHAR(7),[columnname],120) AS Month

Regards,
Sushant
DBA
West Indies

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 00:19:11

why do you need to convert date values to varchar. If its for presentation issue you may be better of doing this at your front end app. changing dates to varchars in sql is not recommended especially if you want to do manipulations using the values.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2011-09-02 : 09:06:14

the front end application is excel and I am not sure I can do that there(because 2011-02-01 indicates sales for whole month of feb, whicch excel can't know)
I dont have to do manipulations on the date...
but if you have any other way, please let me know.
Thanks.

Regards,
Sushant
DBA
West Indies

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 12:56:04

for reducing date to month date do like


SELECT DATEADD(mm,DATEDIFF(mm,0,datefield),0)...
...

so all dates will reduce to month date for a month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skybvi
Posting Yak Master

193 Posts

Posted - 2011-09-06 : 08:55:02

cool
thanks..

Regards,
Sushant
DBA
West Indies

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 11:09:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources