Help - Just using the time of DateTime or SmallDateTime

I need to store the time in a table ("10:00:00 AM") and then compare just
the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
this is simple as heck but because I am a newbie I am stumbling. Can
someone provide me with a sample of this? Thank you.What datatype is the column in the table of?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Code Boy" <CodeBoy@.microsoft.com> wrote in message news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.ph
x.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
> the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
> this is simple as heck but because I am a newbie I am stumbling. Can
> someone provide me with a sample of this? Thank you.
>|||That is up to me (so whatever you tell me). I just want to end up selecting
(with T-SQL) any row where a column has a time only greater then the time
portion of the current time stamp. Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMb8aRNVGHA.4740@.TK2MSFTNGP14.phx.gbl...
> What datatype is the column in the table of?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Code Boy" <CodeBoy@.microsoft.com> wrote in message
> news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...|||code
select convert(varchar(8),current_timestamp,108
) , it will give you
time.
You have to compare it with time in your table.
Look at convert function for more details in BOL.
Regards
Amish Shah.|||When I do this the compare to the time in my table does not select anyting.
It is as if now that they are varchars the >= operator does not work
properly. I will look in BOL for more info. Thank you.
"amish" <shahamishm@.gmail.com> wrote in message
news:1143820529.897848.99710@.g10g2000cwb.googlegroups.com...
> code
> select convert(varchar(8),current_timestamp,108
) , it will give you
> time.
> You have to compare it with time in your table.
> Look at convert function for more details in BOL.
> Regards
> Amish Shah.
>|||>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL.
Okay, so when you insert the data into SQL Server, make sure the date is not
included (or manually force it to be 1900-01-01 on insert).
Now, you can compare to CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE()). An
example:
CREATE TABLE #foo
(
FooID INT,
NextBar SMALLDATETIME
)
INSERT #foo SELECT 1, '1900-01-01 18:34';
INSERT #foo SELECT 2, '02:25'; -- 1900-01-01 is the default
INSERT #foo SELECT 3, '06:34';
INSERT #foo SELECT 4, '10:25';
INSERT #foo SELECT 5, '14:57';
INSERT #foo SELECT 6, '20:36';
INSERT #foo SELECT 6, '23:11';
-- now let's find those rows where NextBar is within the next 6 hours
DECLARE @.s SMALLDATETIME, @.e SMALLDATETIME;
SET @.s = CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE());
SET @.e = DATEADD(HOUR, 6, @.s);
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo
WHERE NextBar >= @.s
AND NextBar <= @.e;
-- see all rows:
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo;
DROP TABLE #foo;|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||> select cast(convert(varchar,OurTime,114) as datetime) as OurTime
> from FileName
> where cast(convert(varchar,OurTime,114) as datetime) >
> convert(varchar,getdate(),114)
All these casts and converts will really hurt your performance.
If you're cunning enough to have an index on that column, you should compare
the plan for this solution compared to the one I provided...