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 |
|
desikankannan
Posting Yak Master
152 Posts |
 Posted - 2010-02-17 : 10:54:53
|
| iam using sql server 2005 , my date is storing 10/02/2002 00:00:00 but i want to display only the datepls guid me iam using stored procedure or i wants to write u functionfor this formatpls give any sql function for this formatDesikankannan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-17 : 10:59:12
|
| thats the way in which sql server store date values in table. Unless you use date datatype which is only available from SQL 2008 you will have associated time part of 00:00:00 even when you store date alone. However, you dont need to be worried about this as you can always use date formatting functions to get it in your desired format at your front end application or by using CONVERT() in T-sql (which i wont recommend unless you dont have front end)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
JohnBGood
Starting Member
48 Posts |
Posted - 2010-02-17 : 12:01:55
|
| Visakh16, do you say that for performance reasons? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-17 : 12:08:07
|
quote:
Originally posted by JohnBGood
Visakh16, do you say that for performance reasons?
Not only that it makes date manipulations and calculations difficult. its not required to change datatype of field just for formatting purpose. Formatting is not something that RDBMS needs to do. It should be done strictly at front end.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-17 : 13:11:01
|
If you format the date at SQL end it changes from DATETIME to a String.If your application needs to do any further manipulation to it then it will have to convert it from String back to Datetime, manipulate it, and then convert it back to String to display it. So better to send it to front end application as Datetime, let the application do any manipulation, including converting it to the appropriate format for display.Conversion of dates between Datetime (native) format and String (and from String to Datetime) is very dependant on the format of the String - and in turn that usually depends on the "Locale" setting of the computer it is running on, so it becomes a major source of bugs etc.For example: 01/02/03 will be interpreted as "Jan 02, 2003" (USA), 01-Feb-2003 (UK) 03-Feb-2001 (Japan)And, yes, it also takes time on the SQL Box making the conversion, so better to leave that to the application - allowing the SQL box to do more database work  |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-18 : 01:13:05
|
quote:
Originally posted by Kristen
If you format the date at SQL end it changes from DATETIME to a String.If your application needs to do any further manipulation to it then it will have to convert it from String back to Datetime, manipulate it, and then convert it back to String to display it. So better to send it to front end application as Datetime, let the application do any manipulation, including converting it to the appropriate format for display.Conversion of dates between Datetime (native) format and String (and from String to Datetime) is very dependant on the format of the String - and in turn that usually depends on the "Locale" setting of the computer it is running on, so it becomes a major source of bugs etc.For example: 01/02/03 will be interpreted as "Jan 02, 2003" (USA), 01-Feb-2003 (UK) 03-Feb-2001 (Japan)And, yes, it also takes time on the SQL Box making the conversion, so better to leave that to the application - allowing the SQL box to do more database work
I really liked the example used for illustration ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
date format display