Search date in SQL

General
1

Hello

I have this query in SQL and work fine . I take the current computer date(Current_TimeStamp and I want to see the same day but from the last week
SQL Query:
DECLARE @Fecha DATE

Set @Fecha = CURRENT_TIMESTAMP
select * from RegisterTotalsHistory
where @Fecha = dateadd(dd,0, datediff(dd,-7,rth_dtDate))

I make my connection in xojo and work fine

Dim db As New ODBCDatabase
Dim sql As String
Dim rs As RecordSet
Dim Today as Date

today = New Date

//Value for the ODBC
db.DataSource = “SMPDQ”
db.UserName = “sa”
db.Password = “suprsonic”

this option work fine for see all the record in the table.
db.SQLExecute(“BEGIN TRANSATION”)
sql = "SELECT rttl_sDescription, rttl_nCount, rttl_nTotal, rta_nArrangementID, rth_dtDate FROM RegisterTotalsHistory "
sql = SQLify(sql)
rs = db.SQLSelect(sql)

But when I Modify the sql with this option that is not working
db.SQLExecute(“BEGIN TRANSATION”)
sql = "SELECT rttl_sDescription, rttl_nCount, rttl_nTotal, rta_nArrangementID, rth_dtDate FROM RegisterTotalsHistory "
sql = sql + “WHERE "
sql = sql + “’” + Today.SQLDateTime + “’”
sql = sql + " = dateadd(dd,0, datediff(dd,-7,rth_dtDate))”
sql = SQLify(sql)
rs = db.SQLSelect(sql)

The RecordSet Show the Message NILL If I use without the where the recordset is fine

Best Regards
Abimael Lopez

2

the problem at first glance this syntax in the next line of your code:

sql = sql + " = dateadd(dd,0, datediff(dd,-7,rth_dtDate))"

grabbing these double assignment “=”

3

Try to use DATETIME2 datatype for logtime column -

Query:

[code]DECLARE @temp TABLE (logtime DATETIME2)
INSERT INTO @temp (logtime)
VALUES
(‘20120925 12:00:00.000’),
(‘20120925 12:59:59.999’),
(‘20120925 13:00:00.000’)

SELECT *
FROM @temp
WHERE logtime BETWEEN ‘2012-09-25 12:00:00.000’ AND ‘2012-09-25 12:59:59.999’
ORDER BY logtime
Output:

logtime

2012-09-25 12:00:00.0000000
2012-09-25 12:59:59.9990000
DATETIME vs DATETIME2:

SELECT name, [precision]
FROM sys.types
WHERE name IN (‘datetime’, ‘datetime2’)
Output:

name precision

datetime2 27
datetime 23[/code]

4

What SQL database engine? SQLite? msSQL? Postgres?

If SQLite… there is no true “date” format, and the above example from Nihir won’t work as the format going in (yyyyyMMdd) does not match the query (yyyy-mm-dd). For the most part SQLite handles “dates” as “text” and it is reccomended to always use the SQLDate and SQLDatetime formats as specified in the LR (yyyy-mm-dd and yy-mm-dd hh:mm:ss)