Date Query: Select all items before 5/Sep/2007

A

ali

I have a table look like this:

ID Name Trade_Date Settle_Date
1 AA Company 20070903 20071005
2 BB Company 20070904 20070910
3 ABC Insitute 20070905 20070913
4 ST Shop 20070906 20071021
----------------------------------------------------------------------------------------
My Question1:


I need an SQL Qeury,

to select all items where the Trade_date is greater 2007/Sep/05

----------------------------------------------------------------------------------------
Question 2:
Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or
dd/mmm/yyyy , something like that ?...



Thank you very much ! ! ^_^!
 
D

Duane Hookom

SELECT *
FROM [look like this]
WHERE Trade_Date > 20070905;

To convert you number ot a date, try an expression like:
DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)
 
A

ali

It doesn't work !!!

I mean, this SQL Query returns all data, it doesn't filter the dates at all. !

does it have anything to do with the date format ? My Data Type for that is
"data/time" and format is "yyyymmdd".

please kindly help me. Thank you so much ^_^!

--
---------


Duane Hookom said:
SELECT *
FROM [look like this]
WHERE Trade_Date > 20070905;

To convert you number ot a date, try an expression like:
DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)

--
Duane Hookom
Microsoft Access MVP


ali said:
I have a table look like this:

ID Name Trade_Date Settle_Date
1 AA Company 20070903 20071005
2 BB Company 20070904 20070910
3 ABC Insitute 20070905 20070913
4 ST Shop 20070906 20071021
----------------------------------------------------------------------------------------
My Question1:


I need an SQL Qeury,

to select all items where the Trade_date is greater 2007/Sep/05

----------------------------------------------------------------------------------------
Question 2:
Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or
dd/mmm/yyyy , something like that ?...



Thank you very much ! ! ^_^!
Click to expand...
Click to expand...
 
D

Duane Hookom

I'm not sure why you didn't mention the date data type in your first posting.
Try:
SELECT *
FROM [look like this and still no name]
WHERE Trade_Date >#09/05/2007#
--
Duane Hookom
Microsoft Access MVP


ali said:
It doesn't work !!!

I mean, this SQL Query returns all data, it doesn't filter the dates at all. !

does it have anything to do with the date format ? My Data Type for that is
"data/time" and format is "yyyymmdd".

please kindly help me. Thank you so much ^_^!

--
---------


Duane Hookom said:
SELECT *
FROM [look like this]
WHERE Trade_Date > 20070905;

To convert you number ot a date, try an expression like:
DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)

--
Duane Hookom
Microsoft Access MVP


ali said:
I have a table look like this:

ID Name Trade_Date Settle_Date
1 AA Company 20070903 20071005
2 BB Company 20070904 20070910
3 ABC Insitute 20070905 20070913
4 ST Shop 20070906 20071021
----------------------------------------------------------------------------------------
My Question1:


I need an SQL Qeury,

to select all items where the Trade_date is greater 2007/Sep/05

----------------------------------------------------------------------------------------
Question 2:
Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or
dd/mmm/yyyy , something like that ?...



Thank you very much ! ! ^_^!
Click to expand...
Click to expand...
Click to expand...
 
A

ali

Hey!, Duane, Big thanks from me!! it works now~!!!
sorry my table's name is "trade1",

can you please explain me what "#" (the sharp sign) means in SQL ?.

Thank you so much !


--
---------


Duane Hookom said:
I'm not sure why you didn't mention the date data type in your first posting.
Try:
SELECT *
FROM [look like this and still no name]
WHERE Trade_Date >#09/05/2007#
--
Duane Hookom
Microsoft Access MVP


ali said:
It doesn't work !!!

I mean, this SQL Query returns all data, it doesn't filter the dates at all. !

does it have anything to do with the date format ? My Data Type for that is
"data/time" and format is "yyyymmdd".

please kindly help me. Thank you so much ^_^!

--
---------


Duane Hookom said:
SELECT *
FROM [look like this]
WHERE Trade_Date > 20070905;

To convert you number ot a date, try an expression like:
DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)

--
Duane Hookom
Microsoft Access MVP


:

I have a table look like this:

ID Name Trade_Date Settle_Date
1 AA Company 20070903 20071005
2 BB Company 20070904 20070910
3 ABC Insitute 20070905 20070913
4 ST Shop 20070906 20071021
----------------------------------------------------------------------------------------
My Question1:


I need an SQL Qeury,

to select all items where the Trade_date is greater 2007/Sep/05

----------------------------------------------------------------------------------------
Question 2:
Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or
dd/mmm/yyyy , something like that ?...



Thank you very much ! ! ^_^!
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

It doesn't work !!!

I mean, this SQL Query returns all data, it doesn't filter the dates at all. !

does it have anything to do with the date format ? My Data Type for that is
"data/time" and format is "yyyymmdd".

please kindly help me. Thank you so much ^_^!
Click to expand...

The *FORMAT* is irrelevant, then. The data is stored as a Double Float count
of days (and fractions of a day, times) since midnight, December 30, 1899 -
regardless of the format.

To get all dates since September 1, use a criterion
#1/9/2007#
or

[Enter start date:]
Click to expand...


John W. Vinson [MVP]
 
D

Duane Hookom

Access uses various delimiters for various data types. You may notice that
text/string values are delimited with either single or double quotes. Dates
are delimited with #. Numbers (my favorite) don't have any delimiter.

MS SQL Server uses single quotes for dates and strings ;-)
--
Duane Hookom
Microsoft Access MVP


ali said:
Hey!, Duane, Big thanks from me!! it works now~!!!
sorry my table's name is "trade1",

can you please explain me what "#" (the sharp sign) means in SQL ?.

Thank you so much !


--
---------


Duane Hookom said:
I'm not sure why you didn't mention the date data type in your first posting.
Try:
SELECT *
FROM [look like this and still no name]
WHERE Trade_Date >#09/05/2007#
--
Duane Hookom
Microsoft Access MVP


ali said:
It doesn't work !!!

I mean, this SQL Query returns all data, it doesn't filter the dates at all. !

does it have anything to do with the date format ? My Data Type for that is
"data/time" and format is "yyyymmdd".

please kindly help me. Thank you so much ^_^!

--
---------


:

SELECT *
FROM [look like this]
WHERE Trade_Date > 20070905;

To convert you number ot a date, try an expression like:
DateSerial([DateField]\10000,([DateField] mod 1000)\100,[DateField] Mod 100)

--
Duane Hookom
Microsoft Access MVP


:

I have a table look like this:

ID Name Trade_Date Settle_Date
1 AA Company 20070903 20071005
2 BB Company 20070904 20070910
3 ABC Insitute 20070905 20070913
4 ST Shop 20070906 20071021
----------------------------------------------------------------------------------------
My Question1:


I need an SQL Qeury,

to select all items where the Trade_date is greater 2007/Sep/05

----------------------------------------------------------------------------------------
Question 2:
Do i have to adjust the date format eg: dd/mm/yyyy or /mm/dd/yyyy or
dd/mmm/yyyy , something like that ?...



Thank you very much ! ! ^_^!
Click to expand...
Click to expand...
Click to expand...
 
Top