What is Select Command in Retrieving Time

[Saimvp]

Hi and Hello.
Good Day.

I have a problem in retrieving time in database. I have Table name "SAMPLE"
and have field name "DATES"


DATES
-------------------------------------|
5/9/2008 1:48:06 PM |
--------------------------------------

* If I will retrive only a date my select command is:
Select convert(nvarchar,Dates,101) as Dates from Sample

OUTPUT: Only the Date 5/9/2008.

My problem is what is the select command to retrieve only the time like the
above data.

I want the output only like this: 1:48:06 PM


Thank you in advance.

 

[Marc Gravell]

First - this is very "off topic"; this is a SQL question, not C#.

Second - note that SQL Server 2008 ships with support for (separate)
date and time data-types, and direct casting etc - so this would be
something to consider in the future.

* If I will retrive only a date my select command is:
Select convert(nvarchar,Dates,101) as Dates from Sample

No; that returns a string that happens to look like the date, but which
is at high risk of i18n issues from the consuming system. Personally I'd
be tempted to worry about this at the consumer (C# code, or a report,
etc) where there is good support for date functions; however, you can do
similar in TSQL (without introducing i18n issues):

DECLARE @When datetime
SET @When = GETDATE()
SELECT @When,
CAST(CAST(@When AS int) AS datetime) AS [Date],
@When - CAST(@When AS int) AS [Time]

I want the output only like this: 1:48:06 PM

Well, format 8 is quite similar; if you need the AM/PM then you would
probably need to trim a 0 or 100. But again; TSQL is not the best place
to format dates/times. That isn't what it is designed for.

Marc

 

[Ignacio Machin ( .NET/ C# MVP )]

Hi and Hello.
Good Day.

I have a problem in retrieving time in database. I have Table name "SAMPLE"
and have field name "DATES"

DATES
-------------------------------------|
5/9/2008 1:48:06 PM      |
--------------------------------------

* If I will retrive only a date my select command is:
Select convert(nvarchar,Dates,101) as Dates from Sample

OUTPUT: Only the Date 5/9/2008.

My problem is what is the select command to retrieve only the time like the
above data.

I want the output only like this: 1:48:06 PM

Thank you in advance.

This is a SQL question, post it in the SQLSERVER.Developer NG