| Author |
Topic |
|
latingntlman
Yak Posting Veteran
96 Posts |
 Posted - 2006-11-08 : 11:06:49
|
I need to show a date in DateTime format. i.e. 11/8/2006 needs to be displayed as 11/8/2006 11:05 AM.does anyone know the syntax.thx,john |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-11-08 : 11:13:16
|
why don't you do this using your front end application ?
KH |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-08 : 11:18:33
|
| [code]declare @date datetimeset @date = getdate()select convert(varchar(10), @date, 101) + substring(convert(varchar(26), @date, 9), 12, 6) + substring(convert(varchar(26), @date, 9), 25, 2)[/code] |
|
|
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2006-11-08 : 11:22:48
|
| Because it sends the data to a Excel spreadsheet and I already tried to format it but it just doesn't want to do it. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-11-08 : 11:28:39
|
| " it just doesn't want to do it." Translation: "I don't know how to do it in Excel so I will have someone do it for me in the back end, where it should not be done"right click your column in excel. choose format cells. then choose the appropriate format from the list.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-11-08 : 11:30:42
|
select convert(char(11), getdate(), 101) + stuff(right(convert(varchar(40), getdate(), 109), 14), 6, 7, ' ')
KH |
|
|
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2006-11-08 : 11:36:34
|
| wow, aged one, already tried that route. If I were you I'd be a little careful what you say or write. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-08 : 11:45:39
|
latingntlman - you'll learn pretty quickly around here not be snippy at veteran posters like DonAtWork. You'll stop getting answers and you might even end up on the twit list  In fact, you can do it in Excel, and it would better to do it there, because then your data stays as a date instead of becoming a string which is pretty useless for anything other than display (try comparing or performing calculations on it). This custom cell format will give you exactly what you want in Excelm/d/yyyy h:mm AM/PM |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-11-08 : 12:08:00
|
I like being snipped at  My answer was hostile, so it deserved a snippy reply.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-08 : 12:59:56
|
Oh trust me, I was not defending you, I was just giving latingntlman some advice because others are not as thick skinned about snippiness as you are! |
|
|
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2006-11-08 : 14:39:54
|
Thanks, snSQL, I used your code and it works beautifully!! |
|
|
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2006-11-08 : 14:53:05
|
| Fellas, I found out the reason it wasn't working in Excel is because on the "Create Table" step of the DTS, I had those date fields as varchar, thus, it places a ' before the value, so you can't format it to date. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-11-08 : 15:50:31
|
| You might want to take a look at your nicely formatted dates using the convert function -- they are STILL just text in Excel since they have been converted to varchar when you forced the formatting to be done in SQL. Try sorting your data based on your "date" column and see what happens.You should always, always use proper datatypes and always do your formatting at the client.- Jeff |
|
|
|
|
DateFormat question