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 |
|
PaTRiCKDRD
Yak Posting Veteran
55 Posts |
 Posted - 2003-11-24 : 14:21:24
|
| Hi guys! I need to write a select clause that gets the date and the time separately from a datetime columns into a result column (alias), one of them the date and the other the time. Also, I need to set my own format for the date. How can I do this? |
|
|
Granick
Starting Member
46 Posts |
Posted - 2003-11-24 : 14:29:19
|
| If you look up CONVERT in the BOL, you will find that you can use it to separate the date and time. As far as specifying your own format for the date, I don't know of a standard function that will allow that, unlike VB or some other languages. Who knows, maybe you will get lucky and one of the formats in the CONVERT function will give you what you need. Either that or you will likely need to write your own UDF.Shannon |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-24 : 14:30:52
|
| select convert(varchar(8),dte,112), convert(varchar(8),dte,8)>> Also, I need to set my own format for the date.Use the string manipulation functions together with date conversion styles.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-11-24 : 19:34:38
|
| format the date at the presentation layer, not with SQL Server. when you format something with SQL Server, you are no longer returning raw data you can further sort or format or compare at the presentation layer.- Jeff |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-11-24 : 20:37:17
|
| If you are returning the data to ASP / VB, look up the FORMAT() function.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-11-25 : 01:57:23
|
I dont think Format works with datetime values in ASP and the FormatDatetime is soooo limited in its functionality!Owais
Please excuse my driving, I am reloading. |
|
|
|
PaTRiCKDRD
Yak Posting Veteran
55 Posts |
Posted - 2003-11-25 : 09:46:19
|
| Reply to nr: "convert(varchar(10),starttime,105) as myDate, convert(varchar(5),starttime,8) as myTime" did the job for me, thanks!But what about ASP and vbscript michaelp and mohdowais?I need to use "for xml" clause to publish my query on the web, will I have any problem?I couldn't find format() function on SQL Server Books online.Could you give me an example guys? I am trying to publish a query to the web for the very first time!Please, if someone can, give me an example!How can I handle this?Thanks in advance guys, keep up the good work! |
|
|
|
|
|
|
|
|
How can I get date and time separately?