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.
| Author |
Topic |
|
ankur_gurha
Starting Member
20 Posts |
 Posted - 2006-08-01 : 05:19:18
|
| How do you convert datetime in 2005-10-29 00:00:00.000 format to DD/MM/YYYY The query by which i retreieve the result is CAST( dbo.GetJobDate(j.Id) AS DateTime) which gives me the result as in 2005-10-29 00:00:00.000 format which i want to convert into DD/MM/YYYY format.. Please suggest..Thanks in advance |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-01 : 05:20:45
|
[code]use convert(varchar(10), date_col, 103)[/code]What data type does dbo.GetJobDate(j.Id) return ? datetime or varchar ?
KH |
 |
|
|
ankur_gurha
Starting Member
20 Posts |
Posted - 2006-08-01 : 05:22:58
|
| DateTime |
|
|
|
ankur_gurha
Starting Member
20 Posts |
Posted - 2006-08-01 : 05:25:14
|
| Thanks mate.. it does the work.. thanks a million |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-08-01 : 05:26:48
|
[code]convert(varchar(10), dbo.GetJobDate(j.Id), 103)[/code]Formatting of datetime is preferably perform at your front end application or reporting tool.
KH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-08-01 : 07:01:09
|
| Formatting dates in SQL is a common question, and "Do it in the application" is frequently given as the answer!!The reasons are:If you format it in SQL Server, rather than your application, it will be transmitted to your application as a String, and thus will not be recognised as a date/time - so your application will not be able to manipulate it as a date/time.Different users may be in different timezones / locales etc. (if not now, then maybe in the future when your application has become MegaApp!), and the formatting you impose in SQL Server may be unsuitable for some of your audience.And it consumes CPU on the server - the server has to services many clients, of course.Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-01 : 07:14:57
|
| Of course there are exceptions.When outputting the result with BCP, the file might have conditions that date format is in a special format, when later processed with another application. The same goes for decimal numbers.Peter LarssonHelsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-08-01 : 11:05:49
|
| If you use front end application then use format function thereMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
DateTime conversion