Formatting date field - 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)

Formatting date field

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-30 : 06:27:07

I have a query


use debt
GO

SELECT distinct d.Code as [Account], k.KeyEventEndDate

FROM Debt AS d
JOIN Debtor as dtr ON dtr.DebtID = d.DebtID
JOIN DebtorKeyEvents AS k ON k.DebtorID = dtr.DebtorID

WHERE k.KeyEventCode = 1 AND
k.KeyEventEndDate Between '2009-10-28' AND '2009-12-31'

ORDER BY k.KeyEventEndDate

is there a way that I can format the k.KeyEventEndDate as UK date format dd/mm/yyyy in the Select so that it does not interfere with the ORDER BY please?

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-10-30 : 06:32:02

you can use the convert function.

WHERE k.KeyEventCode = 1 AND
convert(k.KeyEventEndDate,varchar(10),103) Between '2009-10-28' AND '2009-12-31'

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 06:36:12

I always find the "set dateformat xxx" before your query handy when dealing with dates outside standard setup. Helps SQL implicitly convert text to dates.

xxx being dmy,ymd etc

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-30 : 06:37:16

That's strange as I now get the error

"'varchar' is not a recognized built-in function name"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-30 : 06:39:06

what is the data type for column KeyEventEndDate ?

KH
[spoiler]Time is always against us[/spoiler]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-30 : 06:49:56

quote:
Originally posted by khtan

what is the data type for column KeyEventEndDate ?

KH
[spoiler]Time is always against us[/spoiler]

It is datetime.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-10-30 : 06:58:38

My mistake

this shd work.
convert(varchar(10),k.KeyEventEndDate,101)

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-30 : 08:45:09

quote:
is there a way that I can format the k.KeyEventEndDate as UK date format dd/mm/yyyy in the Select so that it does not interfere with the ORDER BY please?

Why do you need to format the date, since KeyEventEndDate is a datetime data type, your query ORDER BY KeyEventEndDate should give you want you want. Is there anything with your query ?

KH
[spoiler]Time is always against us[/spoiler]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-30 : 09:13:42

quote:
Originally posted by khtan

quote:
is there a way that I can format the k.KeyEventEndDate as UK date format dd/mm/yyyy in the Select so that it does not interfere with the ORDER BY please?

Why do you need to format the date, since KeyEventEndDate is a datetime data type, your query ORDER BY KeyEventEndDate should give you want you want. Is there anything with your query ?

KH
[spoiler]Time is always against us[/spoiler]

Currently the script returns:

100201 2009-11-02 00:00:00
234112 2009-12-04 00:00:00
etc...

but I require the format

100201 02-11-2009
234112 04-12-2009

Can this be done please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 09:19:24

quote:
Originally posted by OldMySQLUser

quote:
Originally posted by khtan

quote:
is there a way that I can format the k.KeyEventEndDate as UK date format dd/mm/yyyy in the Select so that it does not interfere with the ORDER BY please?

Why do you need to format the date, since KeyEventEndDate is a datetime data type, your query ORDER BY KeyEventEndDate should give you want you want. Is there anything with your query ?

KH
[spoiler]Time is always against us[/spoiler]

Currently the script returns:

100201 2009-11-02 00:00:00
234112 2009-12-04 00:00:00
etc...

but I require the format

100201 02-11-2009
234112 04-12-2009

Can this be done please?

Where do you want to show the data?

Madhivanan

Failing to plan is Planning to fail

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 10:22:45

that is italian format, apparently

SELECT convert(char(10),convert(datetime,'2009-11-02 00:00:00'),105)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources