CODEWITHSUNDEEP
sqldatabasesqlitedatetime
ThN
ThN

Reputation: 3276

Does constructor DateTime take string or ONLY TIMESTAMP type as parameter?

I am working with SQLite SQL statement or Query.

So far, I was able to figure out some of things by asking stackoverflow question that can cause problem when executing such SQL Statement.

However, I still am not able to get any result?

Here is my SQL Query:

SELECT * 
FROM Alarms 
WHERE ALARMSTATE IN (0,1,2) 
  AND ALARMPRIORITY IN (0,1,2,3,4) 
  AND ALARMGROUP IN (0,1,2,3,4,5,6,7) 
  AND DateTime(ALARMTIME) BETWEEN datetime("2012-08-02 00:00:00") 
                    AND datetime("2012-08-03 00:00:00") 
ORDER BY ALARMTIME DESC

Here is my table as viewed within datagridView control: As you can see, I have records from yesterdays and the day before in the table.

enter image description here

I corrected all the columns in question of their format such as my ALARMTIME, which is TEXT in data type. Still not getting any result, I decided to run the SQL statement right from within SQLite Administrator application as below. Surprisingly, I got the same result which is nothing or nill or null for dataset. However, SQLite Administrator showed me that DateTime accepts only TIMESTAMP as a parameter not TEXT. If so, my above SQL Statement won't work even if I have everything else correct. Am I correct in saying that?

enter image description here

Upvotes: 0

Views: 387

Answers (2)

Malachi
Malachi

Reputation: 3221

try using between 8/1 and 8/3 and you will get the ones for 8/2.

the way you have it should pull up the alarms in between the two times though

EDIT

can you change your ALARMTIME to a DATETIME format? Then you would be able to use the Between and you wouldn't have to convert the text to DATETIME.

if you Convert to DATETIME type you will have better flexibility in your query. it will be easier to use in the future as well.

I believe that DATETIME is a overloaded text type, so it should output text

Upvotes: 0

ThN
ThN

Reputation: 3276

Here is my answer. From what I understand, DATETIME supposed to take string or timestamp as a parameter, but in my case it doesn't work for me.

Since my column ALARMTIME is TEXT, I am able to query my table with the following SQL statement and retrieve dataset I am looking for.

SELECT *
FROM Alarms
WHERE ALARMSTATE IN (0,1,2)
  AND ALARMPRIORITY IN (0,1,2,3,4)
  AND ALARMGROUP IN (0,1,2,3,4,5,6,7)
  AND ALARMTIME LIKE "2012/08/01%"
  ORDER BY ALARMTIME DESC

I simply can't convert my ALARMTIME text into datetime type.

Upvotes: 1

Related Questions