Database dates (Access and SQL Server)

[Wokawidget]

Ok, I have a date field in my Database, Modified_Date, which stores the date as 27/08/02 13:57:55.
If I wanted to select anything that had been changed since I last loaded, ie anything greater than 27/08/02 13:57:55 what would my SQL SELECT statement look like?

SELECT *
FROM tblTable
WHERE Modified_Date >  This is the bit I can't work out...:(

I really should know it, but can't quite get it to work...

Anyone...

Call myself a pro VB Developer...*SIGH* Bad Woka *SLAP*

[Hack]

My suggestion would be to place two textboxs on your screen (start/end date).

Then, code a BETWEEN in your SQL query to look for records that fall between those input dates.

[kleinma]

well how would you know the last date since last loaded? when you do load the data.. do you store that date/time anywhere to do the compare with?

[Hack]

A SELECT MAX(datemodified) should give you the latest modification date.

No?

[swatty]

Should i


ok try this

"SELECT *
FROM tblTable
WHERE Modified_Date >= #" & Format(start, "dd/mm/yy hh:mm:ss") & "#"

This is if u are sure it is stored in that format.



[EDIT]
You know start is a variable with a date in it
[/EDIT]

[kleinma]

Originally posted by Hack
A SELECT MAX(datemodified) should give you the latest modification date.

No?

thats what i was thinking... but if you then did

select * from table where Modified_Date > "THE SELECT MAX VALUE"

you won't get any data.. because no Modified_Date will be greater than the max you just pulled...

[Frans C]

SELECT *
FROM tblTable
WHERE Modified_Date > '2002/08/27 13:57:55'

If you specify the date in ISO 8601 format, both Access as SQL server should understand it. I'm not sure if Access 97 and earlier can understand the single quote, maybe you need the # sign instead.

EDIT: This is the way if the field is a Date field. The way the date is displayed has nothing to do with the way it is stored. If you used a text field, you have a problem.

[Hack]

Originally posted by Kleinma
thats what i was thinking... but if you then did

select * from table where Modified_Date > "THE SELECT MAX VALUE"

you won't get any data.. because no Modified_Date will be greater than the max you just pulled...

Righto...so it seems that two queries might be the answer. The first would get the last modified date, store it into a variable, which could be used in the second query.

[kleinma]

Originally posted by Frans C
SELECT *
FROM tblTable
WHERE Modified_Date > '2002/08/27 13:57:55'

If you specify the date in ISO 8601 format, both Access as SQL server should understand it. I'm not sure if Access 97 and earlier can understand the single quote, maybe you need the # sign instead.

EDIT: This is the way if the field is a Date field. The way the date is displayed has nothing to do with the way it is stored. If you used a text field, you have a problem.

is this all he wanted??? man if so I think the rest of us were looking at the problem a little to hard...

I guess woka just wanted syntax

[Wokawidget]

Arrr...but if you do a Refresh on the collection, say 10 minutes later, then there may be a modified date that is greater
When I load my records into a collection I store the maximum Modified_Date field in a modular level varible, then when I do I refresh it only loads records that have been updated, quicker than loading the whole recordset again
I am using the Timestamp field type in SQL, but Access doesn't have this, no my DLL will not work if I point it towards an Access database. If, instead of a timestamp field, I use a Modified_Date field then this is easily transferable to Access or SQL Server...

Swatty - I take that as I compliment

[kleinma]

Originally posted by Wokawidget
Arrr...but if you do a Refresh on the collection, say 10 minutes later, then there may be a modified date that is greater
When I load my records into a collection I store the maximum Modified_Date field in a modular level varible, then when I do I refresh it only loads records that have been updated, quicker than loading the whole recordset again
I am using the Timestamp field type in SQL, but Access doesn't have this, no my DLL will not work if I point it towards an Access database. If, instead of a timestamp field, I use a Modified_Date field then this is easily transferable to Access or SQL Server...

Swatty - I take that as I compliment

you also have to remember that sql likes ' around a date while access like # around a date...

[Wokawidget]

Which is correct...?

WHERE Modified_Date > '2002/08/27 13:57:55'

OR

WHERE Modified_Date > #2002/08/27 13:57:55#

Woka - having a crappy day - widget

[kleinma]

Originally posted by Wokawidget
Which is correct...?

WHERE Modified_Date > '2002/08/27 13:57:55'

OR

WHERE Modified_Date > #2002/08/27 13:57:55#

Woka - having a crappy day - widget

read my post above this one

[Wokawidget]

U posted that while I was typing

D'oh!

[Wokawidget]

Tis OK though, coz I would only have to update one function in the 3rd Tier (Data Tier) of my app...

Public Function NormailseDate(ByVal pdteDate As Date) As String
Dim strTemp   As String
   strTemp = "#" & Format(pdteDate, "mm/dd/yyyy hh:nn:ss") & "#"
   NormaliseDate = strTemp
End Function

Woka

[kleinma]

Originally posted by Wokawidget
strTemp = "#" & Format(pdteDate, "mm/dd/yyyy hh:nn:ss") & "#"

Woka

WATCH THOSE N's!!!!!!

[Wokawidget]

G'buh?

[swatty]

Originally posted by kleinma
WATCH THOSE N's!!!!!!

Could it be those were nanoseconds but don't they have to be put after the seconds ???


Which is correct...?

WHERE some_text like 'Woka Woka Woka said packm%'

OR

WHERE some_text like 'Woka Woka Woka said packm*'

All depends on the db your testing against