Accessing data from database between given time stamps

[tn_sudheer]

Hello all.,
Hope all dng gud..

I have a application where I will log my data in ms SQL every one hour..

So for reporting purpose I will fetch data from db, based on time stamps.

Let's say I want to fetch data in between 12-07-2021 8:00:00 to 13-07-2021 8:00:00 (i.e 24 hrs data)

So for this my query is like this.
Select value 1 from table1 where timestring between '12-07-2021 8:00:00' And '13-07-2021 8:00:00'.

So as a result I am getting data like this

12-07-2021 8:00:00 to 12-07-2021 23:00:00 ,
13-06-2021 00:00:00 to 13-06-2021 23:00:00,
13-07-2021 00:00:00 to 13-07-2021 8:00:00.

So as per my query I am not requesting data on month of 6th, but here I was getting., How..??

Can anyone suggest me where I was getting wrong..??

 

[Andrzejek]

How is you [tt]timestring[/tt] field defined in your [tt]table1[/tt]?

Looks to me like it is a String/Text, NOT a Date.
So, you may have there values like:

[pre]
timestring
12-07-2021 8:00:00
Bob
12-07-2021 23:00:00
13-06-2021 00:00:00
Susie
13-06-2021 23:00:00
13-07-2021 00:00:00
Dog
13-07-2021 8:00:00
[/pre]

If that is the case, try[tt]
Select value 1 from table1 where timestring between [blue]
CAST([/blue]'12-07-2021 8:00:00'[blue] AS datetime)[/blue] And [blue]CAST([/blue]'13-07-2021 8:00:00' [blue]AS datetime)[/blue]
[/tt]
---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

tn_sudheer,

Date/Time values are NOT the display values that you and I associate with
dd-mm-yyyy hh:mm:ss

You will also need to CAST(timestring) as well in Andy's SQL or use the REAL Date/Time value that you used to get timestring.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[tn_sudheer]

Gud Mng,

My column "TimeString" is of char(26) datatype..

ho to workout from this..

here is my attachment

 

[Andrzejek]

The easiest fix would be to declare "TimeString" field as [tt]DATETIME[/tt]
(I would also rename this field since it would no longer be a String field...)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[tn_sudheer]

Hai @Andrz

I tried of converting my data type from char to datatime datatype.

but it results in error showing a message

- Unable to modify table.
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
The statement has been terminated.

 

[Andrzejek]

If you have data in your field, (usually) you cannot just say: change the data type from X to Y. What you can always do is to add a new field to your table with correct data type and then, eventually, eliminate the field not used any more.
Also, remember to modify the place where you Insert the data into this field.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Any luck with your issue? Do you have all of that resolved?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[tn_sudheer]

Hello Andrz have a look at this

i wannna know how the search is working with this to queries..

Have a look at attachments.

 

[Andrzejek]

How is the TIMESTRING field defined in your table?
And what do you mean by: "how the search is working"?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[tn_sudheer]

Defined timestring as char(26)..

In both queries I am accessing same data with from , to time stamps.

Let's say example accessing data from 1/07/2021 8:00:00 to 2/7/2021 8:00:00.

If I split the time stamp in two parts 1/7)2021 8:00:00 to 1/7/2021 23:00:00 and 2/7/2021 00:00:00 to 2/7/2021 8:00:00 than only I was getting correct fetch result. U can get in above attachment

If I search for directly from 1/7/2021 8:00:00 to 2/7/2021 8:00:00.

In between I am getting 2/6/2021 data also. How come it's coming..??

In that point I raised question how search is working..??

 

[Andrzejek]

timestring as char(26).." so your field is a character field, NOT a Date

In this case, as stated before, you need to CAST (convert) your data from being just characters to something that your data base will consider as DATE:
[tt]
... where [blue]CAST([/blue]timestring [blue]AS datetime)[/blue] between
[blue]CAST([/blue]'12-07-2021 8:00:00'[blue] AS datetime)[/blue] And [blue]CAST([/blue]'13-07-2021 8:00:00' [blue]AS datetime)[/blue][/tt]

"In both queries I am accessing same data with from , to time stamps." - no, you don't.
YOU look at your data and you see dates, your DATABASE looks at your data and it sees just simple text that has nothing to do with dates.

Again, you can either
[ul][li]CAST your data, or[/li]
[li]declare your field as DATETIME[/li]
[/ul]

Edit:
You also have another option. You may keep your char(26) data in the format ‘YYYY-MM-DD HH:mm:SS’ (if you insist to keep your ‘dates’ in a char field and do not want to CAST, this would be the best way to keep it, IMHO), so your data would look like:
[tt]
2021-01-07 08:00:00
2021-01-07 23:00:00
2021-02-07 08:00:00
[/tt]
---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson