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 |
|
dnf999
Constraint Violating Yak Guru
253 Posts |
 Posted - 2007-04-23 : 11:48:07
|
| Hi I have a field Invoice_Date in the format yyyy-mm-dd HH:MM:SS.SSSi.e. 2006-03-31 15:15:20.045I need this as the format dd/mm/yyyy i.e 31/03/2006The Invoice_date field is in datetime, so when I try datepart(dd, Invoice_date) + '/' + datepart(mm, Invoice_date) + '/' + datepart(yyyy, Invoice_Date) it throws an error:Conversion failed when converting the data value '/' varchar to int...What is the best way to do this?Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-23 : 11:51:06
|
| select convert(varchar, Invoice_Date, 103) should give you the date in DD/MM/YYYY format. Check out BOL for CONVERT function for more date formats.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dnf999
Constraint Violating Yak Guru
253 Posts |
Posted - 2007-04-23 : 11:56:45
|
| Thanks Dinakar!Just wondering where in Help can i find such conversions (i.e. 103 representing dd/mm/yyyy).Thanks |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-23 : 12:02:28
|
| How and where are you outputting these dates? (i.e., report, .net web page, etc ) THAT is where you should format your dates, never at the database layer.By trying to do this in T-SQL, you are simply converting your nice DateTime values to STRINGS, which are no longer dates. Just return the datetimes values to your client and let it do the formatting, which it is infinitely more capable of handling with simple mm/dd/yyyy designations or regional/computer defaults and the like, whether it is a report, web page, etc.see: http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspxIt is very bad practice to convert all of your data to VARCHAR when returning results from a database, and it is much, much easier and shorter and more flexible to do this at the presentation layer -- that is what it is there for!- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-04-23 : 12:07:59
|
quote:
Originally posted by dnf999
Thanks Dinakar!Just wondering where in Help can i find such conversions (i.e. 103 representing dd/mm/yyyy).Thanks
As Dinakar already mentioned, look for the CONVERT function in Books Online for more information about the different date formats.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-23 : 12:51:43
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|
|
DateTime to dd/mm/yyy format....?