Playing with dates in SQLite - 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

Playing with dates in SQLite

Author

Topic

avinash.natekar
Starting Member

5 Posts

Posted - 2012-10-11 : 08:00:46

My Table has timestamp in 'MM\DD\YYYY HH:MM:SS' format but its varchar. How can I get last 7 days data. I know in SQL Server its easy but not able to acheive this in SQLite
Could you guys help out here ?

Avinash
-QA(Automation)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-11 : 08:56:30

Check this link, You can able to get solution

http://www.sqlite.org/lang_datefunc.html

SELECT * FROM myTable WHERE DATE(timeStamp) >= DATE('now', 'weekday 0', '-7 days');

--
Chandu

avinash.natekar
Starting Member

5 Posts

Posted - 2012-10-16 : 07:48:23

Hey bandi , what I missed is timestamp format is MM\DD\YYYY HH:MM:SS AM\PM' format which is not supported as timestring in the sqlite datetime functions

Avinash
-QA(Automation)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-16 : 08:04:07

quote:
Originally posted by avinash.natekar

Hey bandi , what I missed is timestamp format is MM\DD\YYYY HH:MM:SS AM\PM' format which is not supported as timestring in the sqlite datetime functions

Avinash
-QA(Automation)

Then use REPLACE('MM\DD\YYYY HH:MM:SS', '\', '-') function to get supported format...

Check these links:
http://www.somacon.com/p370.php
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources