format datetime - 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

SQL Server 2005 Forums

Transact-SQL (2005)

format datetime

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-02 : 04:39:27

Hi,

I have a select statement where i return getdate(). I need to convert the date 2010-03-02 11:39:00 to dd.mm.yyyy.

How can i do that?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

xflekacm
Starting Member

2 Posts

Posted - 2010-03-02 : 04:44:22

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2010-03-02 : 04:45:16

You can use

SELECT CONVERT(VARCHAR(10), GETDATE(), 101)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-02 : 04:47:35

select convert(varchar,getdate(),104)

select replace(convert(varchar,getdate(),103),'/','.')

Note:Varchar datatype

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-02 : 04:51:01

Thanks for the replies. The first reply is in the format i want. The second one returns 03/02/2010.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 04:53:14

quote:
Originally posted by collie

Hi,

I have a select statement where i return getdate(). I need to convert the date 2010-03-02 11:39:00 to dd.mm.yyyy.

How can i do that?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Where do you want to show formatted dates?

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-02 : 05:08:57

In the report created in ssrs 2005

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 06:24:48

quote:
Originally posted by collie

In the report created in ssrs 2005

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Can't you do the formation at the Report?

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-02 : 07:13:51

yea but i prefer to do it in sql.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 07:18:50

quote:
Originally posted by collie

yea but i prefer to do it in sql.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Why?

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-02 : 07:58:04

easier :) why not?

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 08:01:39

quote:
Originally posted by collie

easier :) why not?

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Easier?

1 Your dates would be VARCHARs so that you cant use Date related operations there
2 You can't sort the result by your converted dates as data would sorted by VARCHARs
3 The index available on the date column will not be used thus causing the performance issues

So do the formation at the Report

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-02 : 08:06:40

Thanks for the explanation. I don't need to sort on this report but i will need on future reports. You saved me a lot of future headaches.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 08:13:31

quote:
Originally posted by collie

Thanks for the explanation. I don't need to sort on this report but i will need on future reports. You saved me a lot of future headaches.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

If you use a report or a web application, always do the formation there. Format using sql only if there is no option

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-02 : 08:34:03

Thanks for always helping out.

It's really very useful information what you gave.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 08:40:55

quote:
Originally posted by collie

Thanks for the explanation. I don't need to sort on this report but i will need on future reports. You saved me a lot of future headaches.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

even then convertion you perform for date formatting in query is expensive so better to follow Madhi's suggestion

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-04 : 02:44:24

How would i format it in ssrs?

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 08:43:07

quote:
Originally posted by collie

How would i format it in ssrs?

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

right click the cell showing date data select properties in formatting tab you've set of standard formats you can select from. alternatively you've expression box where you can write expressions for custom formatting also

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources