Working with Date

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,
I am trying to figure out how to get the systems date for
a date based query. I got my query working fine with the
use of GetDate but found that this was to exact because it
also looks at the system time.

I have tried Date but got an error. I tried to use
DatePart but I must not be using it right because I am
getting a syntax error.

What I am looking for is a date formatted like 8/5/2004

Thanks for any help!!!
 
Hi,
I am trying to figure out how to get the systems date for
a date based query. I got my query working fine with the
use of GetDate but found that this was to exact because it
also looks at the system time.

I have tried Date but got an error. I tried to use
DatePart but I must not be using it right because I am
getting a syntax error.

What I am looking for is a date formatted like 8/5/2004

Thanks for any help!!!
Click to expand...


It sounds as though you wish to use the date as criteria for a query.
The current date is Date().
The current date and time is Now().

As criteria in a query you would use:
Where [ADateField] Between Date() and Date() + 7
to return records for the current date and 7 days from today.

The value of the date is a Double number.
Today's date is 38204.
If you store the value in a table field that is what is stored ...
regardless of how you display it.

To actually display the date as you show it, you could use
Format(Date(),"m/d/yyyy")

In a report or form, you could simply set the control source of an
unbound control to = Date() and set the Format property to m/d/yyyy

Hope this helps.
 
John said:
Hi,
I am trying to figure out how to get the systems date for
a date based query. I got my query working fine with the
use of GetDate but found that this was to exact because it
also looks at the system time.

I have tried Date but got an error. I tried to use
DatePart but I must not be using it right because I am
getting a syntax error.

What I am looking for is a date formatted like 8/5/2004
Click to expand...
Hi John,

PMFBI

If GetDate works, then you must be "in" SQL Server?

If so, then I believe this might be what you are looking for:

WHERE DateField = CONVERT(DATETIME, GetDate(), 101)

// style 3 = dd/mm/yy
// style 103 = dd/mm/yyyy (British/French)
// style 102 = yyyy.mm.dd (ANSI)
// style 1 = mm/dd/yy (USA)
// style 101 = mm/dd/yyyy (USA)
// style 110 = mm-dd-yyyy (USA)
// style 8 = hh:mm:ss
// style 100 = mon dd yyyy hh:mi AM or PM (default)
// style 109 = mon dd yyyy hh:mi:ss:mmm AM or PM (default + miilisecs)
// style 107 = Mon dd, yyyy
// style 120 = yyyy-mm-dd hh:mi:ss (24hr) (ODBC canonical)

Apologies again for butting in (especially if I am wrong).

Good luck,

Gary Walter
 
Back
Top