Get rows between current date and current time

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

Get rows between current date and current time

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-08 : 12:11:10

I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77

Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime

If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow

ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77

I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

lack of idea to put the TIME condition.

Plz help me..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-08 : 13:17:29

[code]SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE CAST(CAST(t1.Date AS varchar(10)) + ' ' + STUFF(CAST(t1.Time AS varchar(6),3,0,':') AS datetime)

>=CAST(DATEADD(dd,-7,GETDATE()) AS smalldatetime) AND
CAST(CAST(t1.Date AS varchar(10)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-08 : 13:22:59

Also i would suggest if possible to store the date and time values in a single datetime field for ease of usage and calculations.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-08 : 18:43:34

just run
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE CAST(CAST(t1.Date AS varchar(10)) + ' ' + STUFF(CAST(t1.Time AS varchar(6),3,0,':') AS datetime)

>=CAST(DATEADD(dd,-7,GETDATE()) AS smalldatetime) AND
CAST(CAST(t1.Date AS varchar(10)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)

i'm fail to fixed the error below

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-09 : 00:19:58

i just fixed the error,

SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE CAST(CAST(t1.Date AS varchar(10)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)

>=CAST(DATEADD(dd,-7,GETDATE()) AS smalldatetime) AND
CAST(CAST(t1.Date AS varchar(10)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)

but got another error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 00:52:11

[code]SET DATEFORMAT mdy

SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE CAST(CAST(t1.Date AS varchar(15)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)

>=CAST(DATEADD(dd,-7,GETDATE()) AS smalldatetime) AND
CAST(CAST(t1.Date AS varchar(10)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)[/code]

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-09 : 01:40:03

Got error--> The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Problem:
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77

Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime

If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow

ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77

I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

lack of idea to put the TIME condition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 01:47:30

quote:
Originally posted by Delinda

Got error--> The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Problem:
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77

Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime

If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow

ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77

I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)

lack of idea to put the TIME condition.

How are you passing the time value and date value?

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-09 : 01:53:37

If pc time=1:48PM, in database stored as 1348. This is integer datatype
If pc time=1:48AM, in database stored as 148. This is integer datatype

If pc date=9 AUG 2005, in database stored as 4/3/2005 12:00:00 AM. My table storing Date and Time in different column. This is my big mistake.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 02:25:36

Here is my attempt of replicating your scenario and result:-

DECLARE @date table
(ID int,
[Date] datetime,
 Time int,
 Value int
)
INSERT into @Date
SELECT 204,  '6/1/2008', 2359 , 90
UNION ALL
SELECT 205,  '6/1/2008', 2250,  99
UNION ALL
SELECT 206,  '6/1/2008', 1950,  88
UNION ALL
SELECT 204,  '6/2/2008', 1125 , 90
UNION ALL
SELECT 205,  '6/2/2008', 1250,  99
UNION ALL
SELECT 206,  '6/2/2008', 2250,  88
UNION ALL
SELECT 204,  '6/5/2008', 2034 , 90
UNION ALL
SELECT 205,  '6/5/2008', 2245,  99
UNION ALL
SELECT 206,  '6/5/2008', 1130,  88
UNION ALL
SELECT 204,  '6/6/2008', 2359 , 90
UNION ALL
SELECT 205,  '6/6/2008', 2250,  99
UNION ALL
SELECT 206,  '6/6/2008', 1950,  88
UNION ALL
SELECT 207,  '6/7/2008', 1845,  77
UNION ALL
SELECT 208,  '6/7/2008', 2255,  77
UNION ALL
SELECt 209,  '6/7/2008', 2140 , 77


SET DATEFORMAT mdy
SELECT
t1.ID, t1.[Date], t1.Time,
CAST(CAST(t1.Date AS varchar(12)) + STUFF(CAST(t1.Time AS varchar(6)),3,0,':')AS datetime) ,
t1.VALUE
FROM @Date t1 
WHERE CAST(CAST(t1.Date AS varchar(12)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)

>=CAST(DATEADD(hh,6,DATEADD(dd,-7,GETDATE())) AS smalldatetime) AND
CAST(CAST(t1.Date AS varchar(12)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)

output
----------------------------------------------------------------
ID          Date                    Time                                VALUE
----------- ----------------------- ----------- ----------------------- -----------
206         2008-06-02 00:00:00.000 2250        2008-06-02 22:50:00.000 88
204         2008-06-05 00:00:00.000 2034        2008-06-05 20:34:00.000 90
205         2008-06-05 00:00:00.000 2245        2008-06-05 22:45:00.000 99
206         2008-06-05 00:00:00.000 1130        2008-06-05 11:30:00.000 88
204         2008-06-06 00:00:00.000 2359        2008-06-06 23:59:00.000 90
205         2008-06-06 00:00:00.000 2250        2008-06-06 22:50:00.000 99
206         2008-06-06 00:00:00.000 1950        2008-06-06 19:50:00.000 88
207         2008-06-07 00:00:00.000 1845        2008-06-07 18:45:00.000 77
208         2008-06-07 00:00:00.000 2255        2008-06-07 22:55:00.000 77
209         2008-06-07 00:00:00.000 2140        2008-06-07 21:40:00.000 77

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-09 : 03:19:54

almost done. here's my table

CREATE TABLE RESULT(
[MAIN_ID] [int] NULL,
[DATE] [smalldatetime] NULL, [TIME] [int] NULL,
[VALUE] [int]
) ON [PRIMARY]

The rows shown a follow
Main_ID | Date | Time | Value
------------------------------------------------------
206 | 4/3/2005 12:00:00 AM | 1930 | 1845
206 | 4/3/2005 12:00:00 AM | 2130 | 1900
...
...

When run this
SET DATEFORMAT mdy
SELECT
t1.Main_ID, t1.[Date], t1.Time,
CAST(CAST(t1.Date AS varchar(12)) + STUFF(CAST(t1.Time AS varchar(6)),3,0,':')AS datetime) ,
t1.VALUE
FROM RESULT
WHERE CAST(CAST(t1.Date AS varchar(12)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)

>=CAST(DATEADD(hh,6,DATEADD(dd,-7,GETDATE())) AS smalldatetime) AND
CAST(CAST(t1.Date AS varchar(12)) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)

there's an error.
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I'm suspect value 4/3/2005 12:00:00 AM generate the error but dont know to fixed.

when do this sql,
select distinct date
from RESULT
where main_id=206

i got this,
date
------------------------------
2004-04-01 00:00:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 04:28:03

one more try:-

SELECT
t1.ID, t1.[Date], t1.Time,
t1.VALUE
FROM @Date t1 
WHERE CAST(CONVERT(varchar(11),t1.Date,121) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)

>=CAST(DATEADD(hh,6,DATEADD(dd,-7,GETDATE())) AS smalldatetime) AND
CAST(CONVERT(varchar(11),t1.Date,121) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-09 : 04:35:14

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 04:49:08

quote:
Originally posted by Delinda

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

there is a space between date and time part

CAST(CONVERT(varchar(11),t1.Date,121) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)
Are you sure you've included it?

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-09 : 08:32:41

i test this
select CONVERT(varchar(11),date_taken,121) as date,
STUFF(CAST(Time AS varchar(6)),3,0,':') as time
FROM RESULT WHERE month(date_taken)='8' and year(date_taken)='2004'
order by time

i see the problem is when
TIME=5, it is become TIME=NULL --> it is suppose 00:05 (this is a 12:05 AM)
TIME=46, it is become TIME=NULL --> it is suppose 00:46 (this is a 12:46 AM)
TIME=101, it is become TIME=10:1 --> it is suppose 1:01
TIME=2048, it is become TIME=20:48 --> this is accurate

that's why, when run
SELECT
t1.ID, t1.[Date], t1.Time,
t1.VALUE
FROM @Date t1
WHERE CAST(CONVERT(varchar(11),t1.Date,121) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)

>=CAST(DATEADD(hh,6,DATEADD(dd,-7,GETDATE())) AS smalldatetime) AND
CAST(CONVERT(varchar(11),t1.Date,121) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)

it showing an error
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

how to adjust the above query to make sure TIME is accurate, then no problem when combine together with date?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources