Problem about Query with time - 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

Problem about Query with time

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-06 : 22:20:47

My table like as follow

Results
Date | Time | Value
---------------------------
4/4/2007 | 0 | 879
4/4/2007 | 5 | 600
4/4/2007 | 10 | 390
4/4/2007 | 15 | 490
4/4/2007 | 20 | 290
...
...
...
4/4/2007 | 100 | 290
4/4/2007 | 105 | 290
4/4/2007 | 110 | 290
...
...
4/4/2007 | 1210 | 290
4/4/2007 | 1215 | 290

Date is DateTime, Time is Integer and Value is Integer

If Time=0, mean 1200 AM
If Time=5, mean 1205 AM
If Time=1210, mean 1210 PM

Let's say Current Date=4 April 2007 and Current Time=1206 AM
I want to display data which is Date>=4 April 2007 and Time>=1206 AM

How to query to get expected result shown as follow
Date | Time | Value
---------------------------
4/4/2007 | 10 | 390
4/4/2007 | 15 | 490
4/4/2007 | 20 | 290
...
...
...
4/4/2007 | 100 | 290
4/4/2007 | 105 | 290
4/4/2007 | 110 | 290
...
...
4/4/2007 | 1210 | 290
4/4/2007 | 1215 | 290

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 03:11:35

What's the datatype of CurrentTime parameter? Seeing the sample data i think its varchar and will be always in format hhmm AM/PM. in that case you can use like this

SELECT * FROM YourTable WHERE Date =@CurrentDate AND Time >= CASE WHEN SUBSTRING(@CurrentTime,CHARINDEX(' ',@CurrentTime)+1,LEN(@CurrentTime))='AM' THEN CAST(LEFT(@CurrentTime,CHARINDEX(' ',@CurrentTime)-1) AS int) - 1200
ELSE CAST(LEFT(@CurrentTime,CHARINDEX(' ',@CurrentTime)-1) AS int) END

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-07 : 05:56:08

current date and current time is taken from pc time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 06:01:52

And wats the datatype of Date & time fields?

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-07 : 07:55:12

Date is a DateTime
Time is a Integer

Results
Date | Time | Value
---------------------------
4/4/2007 | 0 | 879
4/4/2007 | 5 | 600
4/4/2007 | 10 | 390
4/4/2007 | 15 | 490
4/4/2007 | 20 | 290
...
...
...
4/4/2007 | 100 | 290
4/4/2007 | 105 | 290
4/4/2007 | 110 | 290
...
...
4/4/2007 | 1210 | 290
4/4/2007 | 1215 | 290

Date is DateTime, Time is Integer and Value is Integer

If Time=0, mean 1200 AM
If Time=5, mean 1205 AM
If Time=1210, mean 1210 PM

If
Date | Time | Value
---------------------------
4/4/2007 | 0 | 879
4/4/2007 | 5 | 600
4/4/2007 | 1210 | 600

How to query to get the result as follow,
Date | Time | Value
---------------------------
4/4/2007 | 00:00 | 879
4/4/2007 | 00:05 | 600
4/4/2007 | 12:10 | 600

...
...

I dont know how convert int to time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 09:46:56

[code]SELECT * FROM Table WHERE DATEADD(mi,Time,Date) >CAST(@PCdate + ' ' + @PcTime AS datetime)[/code]

where @PCDate & @PCTime are date & time value passed

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-07 : 10:24:32

SELECT distinct Time FROM SEL
WHERE DATEADD(mi,Time,Date_Taken) >CAST(4/3/2003 + ' ' + 1018 AS datetime)

It still showing
Time
--------
0
5
6

How to display
Time
--------
00:00
00:06
00:05
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 12:40:40

quote:
Originally posted by Delinda

SELECT distinct Time FROM SEL
WHERE DATEADD(mi,Time,Date_Taken) >CAST(4/3/2003 + ' ' + 1018 AS datetime)

It still showing
Time
--------
0
5
6

How to display
Time
--------
00:00
00:06
00:05
...

use CONVERT(varchar(5),DATEADD(mi,Time,Date),108)

SELECT Date,CONVERT(varchar(5),DATEADD(mi,Time,Date),108),Value FROM Table WHERE DATEADD(mi,Time,Date) >CAST(@PCdate + ' ' + @PcTime AS datetime)

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-07 : 13:56:50

yes... yes.. it's really work. if current time=1:55
How to convert to integer and shown as follow
Time
-------
1:55

Time (Int)
----------
155

:0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 14:25:44

SELECT CAST(REPLACE(Time,':','') AS int)

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-07 : 14:45:59

current is 2:44:44

SELECT CAST(REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','') AS int)
i got 24344, how to omit 44 to become 243 only?