query help - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

Ambikaa
Starting Member

43 Posts

Posted - 2009-02-23 : 06:50:05

Hi,

How to give input to time column in condition statement?

original data
BeginTime
12/30/1899 13:00:00 PM
12/30/1899 7:00:00 PM
12/30/1899 8:00:00 PM
12/30/1899 8:15:00 PM
12/30/1899 7:10:00 PM

and my input from UI interface is 13:00 and some times, it can be 12:00 pm

and i used the below query
SELECT (EventDate+BeginTime) AS BT, (EventDate+EndTime) AS ET, Station, Program
FROM demo3 where begintime='13:00'

but, i didn't get any result

tell me how to give the input to it based on the stored value in the table.

clandestine
Starting Member

6 Posts

Posted - 2009-02-23 : 07:04:48

BeginTime
12/30/1899 1:00:00 AM
12/30/1899 7:00:00 PM
12/30/1899 8:00:00 PM
12/30/1899 8:15:00 PM
12/30/1899 7:10:00 PM

There isn't a row that has a begin time of 1pm in the afternoon so hmm you wouldn't get any results!

Ambikaa
Starting Member

43 Posts

Posted - 2009-02-23 : 07:09:07

quote:
Originally posted by clandestine

BeginTime
12/30/1899 1:00:00 AM
12/30/1899 7:00:00 PM
12/30/1899 8:00:00 PM
12/30/1899 8:15:00 PM
12/30/1899 7:10:00 PM

There isn't a row that has a begin time of 1pm in the afternoon so hmm you wouldn't get any results!

I didn't get any o/p eventhough the table has the value of above input.

clandestine
Starting Member

6 Posts

Posted - 2009-02-23 : 07:16:39

Use the DatePart function

Where DatePart(hh, beginTime) = 13

so if you want 1 am in the morning it would be:

Where DatePart(hh, beginTime) = 1

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 07:20:25

Try this one

declare @a table (id int, dat datetime)

insert into @a values(1,'2009-02-23 17:00:00.000')
insert into @a values(2,'2009-02-23 12:00:00.000')
insert into @a values(3,'2009-02-23 13:00:00.000')
insert into @a values(4,'2009-02-23 05:00:00.000')

select * from @a Where convert(varchar(5),dat,108) ='17:00'

Karthik

matty
Posting Yak Master

161 Posts

Posted - 2009-02-23 : 07:23:31

WHERE begintime = dateadd(day,-2,convert(datetime,'13:00'))

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 07:41:06

quote:
Originally posted by matty

WHERE begintime = dateadd(day,-2,convert(datetime,'13:00'))

I guess this won't work properly.

Karthik

Ambikaa
Starting Member

43 Posts

Posted - 2009-02-23 : 08:02:54

quote:
Originally posted by karthik_padbanaban

Try this one

declare @a table (id int, dat datetime)

insert into @a values(1,'2009-02-23 17:00:00.000')
insert into @a values(2,'2009-02-23 12:00:00.000')
insert into @a values(3,'2009-02-23 13:00:00.000')
insert into @a values(4,'2009-02-23 05:00:00.000')

select * from @a Where convert(varchar(5),dat,108) ='17:00'

Karthik

Hi,

thanks 4r ur reply. The above one works but, how can i give the input like 12:00 pm and 12:00 am (it is from UI )

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 08:32:16

try to change the format as 24hr format in UI itself.

I would be better.

Karthik

matty
Posting Yak Master

161 Posts

Posted - 2009-02-24 : 00:19:10

This will work if input is given as AM/PM provided your date part of begintime is always '12/30/1899'

declare @Sample table (id int, begintime datetime)

insert into @Sample values(1,'12/30/1899 13:00:00 PM')
insert into @Sample values(2,'12/30/1899 12:00:00 PM')
insert into @Sample values(3,'12/30/1899 12:00:00 AM')
insert into @Sample values(4,'12/30/1899 8:00:00 PM')
insert into @Sample values(5,'12/30/1899 8:15:00 PM')
insert into @Sample values(6,'12/30/1899 7:10:00 PM')
insert into @Sample values(7,'12/30/1899 13:00:00.000')

select * from @Sample
where begintime = dateadd(day,-2,convert(datetime,'12:00 AM'))

quote:
Originally posted by matty

WHERE begintime = dateadd(day,-2,convert(datetime,'13:00'))

Ambikaa
Starting Member

43 Posts

Posted - 2009-02-24 : 01:43:23

quote:
Originally posted by matty

This will work if input is given as AM/PM provided your date part of begintime is always '12/30/1899'

declare @Sample table (id int, begintime datetime)

insert into @Sample values(1,'12/30/1899 13:00:00 PM')
insert into @Sample values(2,'12/30/1899 12:00:00 PM')
insert into @Sample values(3,'12/30/1899 12:00:00 AM')
insert into @Sample values(4,'12/30/1899 8:00:00 PM')
insert into @Sample values(5,'12/30/1899 8:15:00 PM')
insert into @Sample values(6,'12/30/1899 7:10:00 PM')
insert into @Sample values(7,'12/30/1899 13:00:00.000')

select * from @Sample
where begintime = dateadd(day,-2,convert(datetime,'12:00 AM'))

quote:
Originally posted by matty

WHERE begintime = dateadd(day,-2,convert(datetime,'13:00'))

Hi,
thanks, it retrieves the record with/without am/pm.

At the same time while inserting new time value into the table, it is inserting with current date and the input and i am not able to change the date as 12/30/1899 from UI.
This is the newly inserted value

2/24/2009 8:00:00 AM

and the date varies as day by day while we inserting value into the table

so, pls suggest me some query with according to the input.

matty
Posting Yak Master

161 Posts

Posted - 2009-02-24 : 02:37:29

SELECT * FROM
(
SELECT CONVERT(VARCHAR(5),begintime,108) AS Time1,* FROM @Sample
)t
WHERE Time1 = CONVERT(DATETIME,'12:00:00 PM')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources