Did I just trip over a golden egg?

I'v been scouring the MSDN library and asking in newsgroups, forums, etc. on a way to extract [only] the time from a DateTime field (for a stored procedure) and no one could answer my question. then I tried this in QA:

selectright(getdate(),7)

and I found an answer. I'm now looking for someone to prove this is either a golden egg, a goose egg, or a documented feature I missed and if so where, so I can learn from it.

Jon

This will work,if you know what the default locale of the Sql Server is set as. However, if you don't, then you could get a nasty shock. For instance, if the server happened to be using ISO8601 dateformats then you would get something meaningless back.

Try

SELECT Right(CONVERT(varchar,GetDate(),126),7)

to see what I mean. The safest way to do what you are doing would be:

SELECT LTRIM(Right(CONVERT(varchar,GetDate(),0),7))

That should guarantee you are getting the correct dateformat string to work with. There's nothing mysterious about the RIGHT function, either - most SQL languages have a substring function.|||

Thank you and I accept the safest way as you suggested. The RIGHT function wasn't mysterious to me but rather the tool I used to get the answer I wanted. I was looking for an easy means to retrieve the time from SQL and was frustrated nobody out there could answer a question I thought would be a standard novice question. I'm realizing the simplest things are the cause of the greatest frustration in the programming world. Thanks for your solution.

Jon

|||Actually, I would suggest this:
SELECT CONVERT(char(12),GETDATE(),114)

For this datetime value:
2005-12-17 20:55:27.060

This would be returned:
20:55:27:060

SeeCAST and CONVERT for more information on how to use these 2 functions.|||

Thank You Terri! I was not familiar with CONVERT and now it seems ALL my date problems can be solved by this new information.

Jon

|||

Hmmm... that's interesting. To tell you the truth, I would have just used a whole bunch of datepart functions myself :)