Query In Between - 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

General SQL Server Forums

New to SQL Server Programming

Query In Between

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-06 : 17:12:46

Hello.

Good Morning.

Anyone can give me an idea on how to query this one.

I have a table for Shifting.

Table Shift

Shf_ID Sht_start shf_end shf_name

01 07:30:00 19:29:59 Day
02 19:30:00 07:29:59 Night

How can I query it that if the current time is 08:01:00 it should display Day.

I have tried using this query but it returns nothing.

select shf_nme from m_shf_001
where shf_flg = 1
and convert(varchar,current_timestamp, 108) between convert(varchar,shf_start, 108) and convert(varchar,shf_end, 108)).

Hope any one could help me.
Thanks

Xiez
Starting Member

13 Posts

Posted - 2012-04-06 : 18:05:53

Well it seems like your table has unneeded data. I'll throw this solution at you first, and if you really want to use the name column, I'll give you a different one :-p

IF current_timestamp BETWEEN (SELECT shf_start FROM Shift WHERE shf_flg = 1) AND (SELECT shf_end FROM Shift WHERE shf_flg = 1)
PRINT 'Day'
ELSE
PRINT 'Night'

Okay, as I started writing this, I realized it's not the nicest looking query in the world... but it should work lol

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-06 : 18:14:53

Thanks Xiez but the reason why did I include the shf_name is that it would be possible to add another shift.

It would also be possible to have afternoon shift.

Xiez
Starting Member

13 Posts

Posted - 2012-04-06 : 18:24:43

I thought you might say that. In that case, you'll want a "CASE" statement. A quick google search will show you the syntax. It's pretty simple. Usually if you only have 2 options, you'd use an IF. If you have more than 2 (shifts, in this case) you'll need to use a ELSE IF or a CASE. I prefer CASE, but it's up to you. Let me know if you need help with the syntax

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-06 : 19:08:00

Thanks but I think it also the same like if.

Case when convert(varchar,current_timestamp, 108)
between convert(varchar,shf_start, 108) and convert(varchar,shf_end, 108) then 'Day'
else 'NIGHT'

Xiez
Starting Member

13 Posts

Posted - 2012-04-06 : 19:30:32

Geez, sorry, my brain is fried today.

Try this:

SELECT shf_name
FROM Shift
WHERE (CURRENT_TIMESTAMP BETWEEN (SELECT shf_start FROM Shift WHERE shf_flg = 1) AND (SELECT shf_end FROM Shift WHERE shf_flg = 1))
OR (CURRENT_TIMESTAMP BETWEEN (SELECT shf_start FROM Shift WHERE shf_flg = 2) AND (SELECT shf_end FROM Shift WHERE shf_flg = 2))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 23:07:07

why are you converting time values to varchar?
what are datatypes of Sht_start shf_end fields?

also why you should use shf_flg here?
I thought requirement was to get shift name by comparing date range against current time and I hope there will be only one shift which matches it in table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-07 : 07:05:28

the data type for shf_start and shf_end was time.
Well i converting it to varchar to get only the time from current_timestamp. I really dont know yet how to get the only the time in current_timestamp.

The purpose of the shf_flg is to query only the active shifts.

If the shf_flg = 0 the it is inactive.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 17:58:47

Well i converting it to varchar to get only the time from current_timestamp
no need of converting to varchar for that. either use logic like in below link or just convert it to be of time datatype.

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

what does shf_flg=2 indicate then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-09 : 21:18:27

Sir Visakh thank you for the post.

I have also use this query but still i cannot get any shift that in the range of start and end shift.

select shf_nme from m_shf_001
where shf_flg = 1
and
CONVERT(time(0),current_timestamp, 108)
between
CONVERT(time(0),shf_start, 108)
and
CONVERT(time(0),shf_end, 108)

shf_flg = 2 indicates nothing it was only enter by Xiez.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:35:31

if shf_start and shf_end are of time datatype

you need just this


select shf_nme from m_shf_001
where shf_flg = 1
and CONVERT(time,GETDATE())>=shf_start
AND CONVERT(time,GETDATE())<=shf_end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-04-10 : 20:37:13

Thank you sir, I have also tried that 1 before but it only returns the DAY when I change my current time to 19:30 to 07:29 it returns nothing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 00:45:29

what do you mean it returns the DAY?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources