convert date results from DB - 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

convert date results from DB

Author

Topic

davidais
Starting Member

17 Posts

Posted - 2012-10-25 : 09:45:44

hi all,

How would I convert this:

select
orderdate
from
salesorders

to return the date as DD-MM-YYYY as opposed to DD.MM.YYYY HH:MM:SS

Many thanks,

D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 10:02:17

for this requirement you need to convert dates to varchar which is not at all recommended. This is more of a presentation logic which you snould try to deal at your front end application using formatting functions.

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-25 : 10:10:52

Just to be pedantic:

declare @foo datetime = getdate()
select convert(varchar(10),@foo,105)

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

davidais
Starting Member

17 Posts

Posted - 2012-10-25 : 10:23:59

why is it not at all recommended to convert dates to varchar?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 10:24:01

quote:
Originally posted by DonAtWork

Just to be pedantic:
declare @foo datetime = getdate()
select convert(varchar(10),@foo,105)

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

this wont give timepart right?

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-25 : 13:31:32

quote:
Originally posted by visakh16

quote:
Originally posted by DonAtWork

Just to be pedantic:
declare @foo datetime = getdate()
select convert(varchar(10),@foo,105)

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

this wont give timepart right?

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

Nope, plain old date

25-10-2012

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

davidais
Starting Member

17 Posts

Posted - 2012-10-26 : 03:39:04

why is it not at all recommended to convert dates to varchar?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-26 : 04:28:13

No reason bot to. Depends on the application.
You can centralise the date format - which means there isn't the issue with different client machines displaying different formats.
You can leave it to the client so that the user can configure the format or the application can display country dependent formats.
Doing it on the client is probably more user friendly but can lead to issues - and depending on the client it might need a specific input format anyway which might be character.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-26 : 09:55:53

quote:
Originally posted by davidais

why is it not at all recommended to convert dates to varchar?

some of reasons why its not recommended
1.If you've to do any further manipulations like date comparison, sorting etc based on converted dates the result may be different if you change base type from datetime.
2. Conversion process itself has some overhead esp in case of large datsets
3. In case you use conversion logic in filters it can even make date condition non sargeable and would ignore indexes if already present in date column
4. If you plan to pass these converted dates to another query as a parameter value it may break based on what format you generate it. date values should always be passed in unambiguos formats

see

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

In any case you've lots of options available in most of application languages which accepts the date values and converts it into format you want so that being case there's no real need to do this at SQL end

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources