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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-03-12 : 09:26:49
|
| Dennis writes "What if I want microseconds displayed with a TO_CHAR? We have a table where we timestamp data with microseconds, but how do I display them? When I use TRIM I can see the microseconds, but not in the format I want. How do I display them with TO_CHAR? For example:SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS PM') FROM dual;This will show me the current system date and time in 'MM/DD/YYYY HH:MI:SS PM' format, like '03/09/2007 05:39:13 PM'. But what I want to see is something like this:'03/09/2007 05:39:13.002330000 PM'Yet there doesn't seem to be a way to format milliseconds.Oddly enough, on this table, if I Trim the timestamp, I can see the milliseconds, but the first part of the date isn't formatted properly. It displays 'DD-MMM-YY HH:MM:SS.000000000 PM' and I need it in like above.Let me know if what you come up with. By the way, this field I am trying to display is TIMESTAMP(9), not VARCHAR or DATE.Thanks! Looking forward to hearing from you." |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-12 : 13:08:14
|
| Use replace on the varchar column before converting to datetimeselect cast(replace(col1, '.000000000', '.000') as datetime)Peter LarssonHelsingborg, Sweden |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-12 : 13:55:28
|
| This is a SQL Server forum, you should try forums.oracle.com or dbforums.com for Oracle questions. |
|
|
|
|
|
|
|
|
Displaying Microseconds