Date format issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date format issue

Author

Topic

DHL
Starting Member

16 Posts

Posted - 2008-01-08 : 07:41:15

Hi,

I have following problem:

My date is like 6/27/2007 12:00:00 AM. How can I change the date format to show 27-Jun-2007 (thus without time and in the given format)? I am using reporting services. There I would like to change the date (in the report itself)...

Please help!

Thanks!

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 08:27:10

quote:
Originally posted by DHL

Hi,

I have following problem:

My date is like 6/27/2007 12:00:00 AM. How can I change the date format to show 27-Jun-2007 (thus without time and in the given format)? I am using reporting services. There I would like to change the date (in the report itself)...

Please help!

Thanks!

Cheers

In Crystal Reports,

Right click on the column
select format
choose the required date format from the available list

I think you can do the same in SSRS as well

Madhivanan

Failing to plan is Planning to fail

DHL
Starting Member

16 Posts

Posted - 2008-01-08 : 09:53:18

Unfortunately, there is no dateformat like this in SSRS. Any other solution?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 10:06:34

You have FormatDate() function in SSRS. have a look at syntax in BOL.

pleitch
Starting Member

8 Posts

Posted - 2008-01-09 : 01:44:54

Just go to the format tab and type in the standard .Net format. For example, I use standard british english dates Day, month, year (used by the majority of the non-us english world).

Then I just put this in to the format field: dd/MM/yyyy
No need for anything before or after it.

d-MMM-YYYY would give you 27-Jun-2007

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-09 : 03:43:13

try with these statements
select convert(varchar(11),getdate(),105)
select convert(varchar(11),getdate(),106)

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-09 : 04:32:29

quote:
Originally posted by sunsanvin

try with these statements
select convert(varchar(11),getdate(),105)
select convert(varchar(11),getdate(),106)

Vinod
Even you learn 1%, Learn it with 100% confidence.

Because formatted dates are to be shown in the report, it is better to format it over there instead of formatting in sql

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 05:49:16

Either use FormatDateTime() function available in expresiion pane of sql reporting services or simply right click on textbox and select format tab and give the format option with 'D' code (2nd one in list)

pleitch
Starting Member

8 Posts

Posted - 2008-01-09 : 21:53:50

quote:
Originally posted by sunsanvin

try with these statements
select convert(varchar(11),getdate(),105)
select convert(varchar(11),getdate(),106)

Vinod
Even you learn 1%, Learn it with 100% confidence.

This will work, but I try to avoid this approach (others in my company prefer this approach). I don't believe that it is the role of the database to do formatting, that is the sole preserve of the user layer. Converting as you have outlined will cause major headaches. Basically, my rule of thumb is that dates should be sent and received as dates (or numbers if absolutely necesary). Otherwise you might have(and I have had) issues of invalid date conversion.

Also - I don't think it is waranted in this case, since reporting services offers a date format - which can be linked to the database user's language preference, or can be manually specified.

That's my preference anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources