How to retrieve Date only without time in Access

[Charless Mohan]

I want to when retrieve data or store date in date/time datatype field. How
can i do?
Thanking You

 

[John W. Vinson]

I want to when retrieve data or store date in date/time datatype field. How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days and
fractions of a day (times). As such, a date/time ALWAYS contains a time
portion. This may be zero, midnight, and such date/time values can be treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time portion") use
the Date() function as its default, or as the source of the date. To enter a
specific date, just enter the date without a time portion - #3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion, such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

 

[Charless Mohan]

Thank You So much.
I want when retrieve its show date only in sql select query how can?
example: Its my code
dt = dbcon.selectvalue("Select refno as [Ref No],cardno as [Card
No],cardname as [Card
Name],stmtdate:Format([stmtdate],("dd/mm/yyyy")",duedate as [Due
Date],totamtdue as [Total Amount],paidamt as [Paid Amount],bank as
[Bank],branch as [Branch], chqnodate as [ChqNo/Date], status as [Status],
acctype as [Acct Type],dueperiod as [Periodical] from Reminder");// where
duedate between '" + BasicDatePicker1.SelectedDate + "' and '" +
BasicDatePicker2.SelectedDate + "'");
here is i want retrieve with format Please tell

I want to when retrieve data or store date in date/time datatype field. How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days and
fractions of a day (times). As such, a date/time ALWAYS contains a time
portion. This may be zero, midnight, and such date/time values can be treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time portion") use
the Date() function as its default, or as the source of the date. To enter a
specific date, just enter the date without a time portion - #3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion, such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

 

[Charless Mohan]

Thanking You.
I want when retrieve by sql select query its show date only.
how can that : select query format for date only retrieve.

I want to when retrieve data or store date in date/time datatype field. How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days and
fractions of a day (times). As such, a date/time ALWAYS contains a time
portion. This may be zero, midnight, and such date/time values can be treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time portion") use
the Date() function as its default, or as the source of the date. To enter a
specific date, just enter the date without a time portion - #3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion, such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

 

[Douglas J. Steele]

Rather than

stmtdate:Format([stmtdate],("dd/mm/yyyy")"

you need

Format([stmtdate],"dd/mm/yyyy") AS statedate,

(As far as I'm aware, you cannot name the alias the same as the original
field, hence statedate rather than stmtdate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank You So much.
I want when retrieve its show date only in sql select query how can?
example: Its my code
dt = dbcon.selectvalue("Select refno as [Ref No],cardno as [Card
No],cardname as [Card
Name],stmtdate:Format([stmtdate],("dd/mm/yyyy")",duedate as [Due
Date],totamtdue as [Total Amount],paidamt as [Paid Amount],bank as
[Bank],branch as [Branch], chqnodate as [ChqNo/Date], status as [Status],
acctype as [Acct Type],dueperiod as [Periodical] from Reminder");// where
duedate between '" + BasicDatePicker1.SelectedDate + "' and '" +
BasicDatePicker2.SelectedDate + "'");
here is i want retrieve with format Please tell

I want to when retrieve data or store date in date/time datatype field.
How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days and
fractions of a day (times). As such, a date/time ALWAYS contains a time
portion. This may be zero, midnight, and such date/time values can be
treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time portion")
use
the Date() function as its default, or as the source of the date. To
enter a
specific date, just enter the date without a time portion - #3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to
midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion,
such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any
stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

 

[John Spencer]

One method (if the field is never null) is

DateValue(DateField)

If your datefield can contains nulls

IIF(IsDate(DateField),DateValue(DateField),Null)

IF you are searching against this field you are better off using a range
that includes the start and end time. So to search for records where
the date of the datefield is Jan 1, 2009 you would use criteria like

WHERE DateField >= #2008-01-01# and DateField <#2008-01-02#



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanking You.
I want when retrieve by sql select query its show date only.
how can that : select query format for date only retrieve.

I want to when retrieve data or store date in date/time datatype field. How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days and
fractions of a day (times). As such, a date/time ALWAYS contains a time
portion. This may be zero, midnight, and such date/time values can be treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time portion") use
the Date() function as its default, or as the source of the date. To enter a
specific date, just enter the date without a time portion - #3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion, such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

 

[George]

I've been successful in using aliases which are the same as the underlying
field name by fully qualifying the table and field in the alias statement. I
don't see the table name in the OP's code, so this is a just a dummy, wher
'tblNameHere represents the table in which the field stmtdate appears.

stmtdate: Format([tblNameHere].[stmtdate],("dd/mm/yyyy")"

Rather than

stmtdate:Format([stmtdate],("dd/mm/yyyy")"

you need

Format([stmtdate],"dd/mm/yyyy") AS statedate,

(As far as I'm aware, you cannot name the alias the same as the original
field, hence statedate rather than stmtdate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank You So much.
I want when retrieve its show date only in sql select query how can?
example: Its my code
dt = dbcon.selectvalue("Select refno as [Ref No],cardno as [Card
No],cardname as [Card
Name],stmtdate:Format([stmtdate],("dd/mm/yyyy")",duedate as [Due
Date],totamtdue as [Total Amount],paidamt as [Paid Amount],bank as
[Bank],branch as [Branch], chqnodate as [ChqNo/Date], status as [Status],
acctype as [Acct Type],dueperiod as [Periodical] from Reminder");// where
duedate between '" + BasicDatePicker1.SelectedDate + "' and '" +
BasicDatePicker2.SelectedDate + "'");
here is i want retrieve with format Please tell

On Fri, 9 Jan 2009 21:56:01 -0800, Charless Mohan

I want to when retrieve data or store date in date/time datatype field.
How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days and
fractions of a day (times). As such, a date/time ALWAYS contains a time
portion. This may be zero, midnight, and such date/time values can be
treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time portion")
use
the Date() function as its default, or as the source of the date. To
enter a
specific date, just enter the date without a time portion - #3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to
midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion,
such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any
stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

 

[Douglas J. Steele]

Your Format statement is wrong, though: you've got an extraneous opening
parenthesis, plus an extraneous double quote at the end.

stmtdate: Format([tblNameHere].[stmtdate],"dd/mm/yyyy")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I've been successful in using aliases which are the same as the underlying
field name by fully qualifying the table and field in the alias statement.
I don't see the table name in the OP's code, so this is a just a dummy,
wher 'tblNameHere represents the table in which the field stmtdate
appears.

stmtdate: Format([tblNameHere].[stmtdate],("dd/mm/yyyy")"

Rather than

stmtdate:Format([stmtdate],("dd/mm/yyyy")"

you need

Format([stmtdate],"dd/mm/yyyy") AS statedate,

(As far as I'm aware, you cannot name the alias the same as the original
field, hence statedate rather than stmtdate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank You So much.
I want when retrieve its show date only in sql select query how can?
example: Its my code
dt = dbcon.selectvalue("Select refno as [Ref No],cardno as [Card
No],cardname as [Card
Name],stmtdate:Format([stmtdate],("dd/mm/yyyy")",duedate as [Due
Date],totamtdue as [Total Amount],paidamt as [Paid Amount],bank as
[Bank],branch as [Branch], chqnodate as [ChqNo/Date], status as
[Status],
acctype as [Acct Type],dueperiod as [Periodical] from Reminder");//
where
duedate between '" + BasicDatePicker1.SelectedDate + "' and '" +
BasicDatePicker2.SelectedDate + "'");
here is i want retrieve with format Please tell

:

On Fri, 9 Jan 2009 21:56:01 -0800, Charless Mohan

I want to when retrieve data or store date in date/time datatype
field. How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days
and
fractions of a day (times). As such, a date/time ALWAYS contains a time
portion. This may be zero, midnight, and such date/time values can be
treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time
portion") use
the Date() function as its default, or as the source of the date. To
enter a
specific date, just enter the date without a time portion -
#3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to
midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion,
such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any
stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

 

[George Hepworth]

More coffee, please. ;(

Thanks for the catch. I guess am too used to UA, where I get a chance to
edit before committing the final post.

Your Format statement is wrong, though: you've got an extraneous opening
parenthesis, plus an extraneous double quote at the end.

stmtdate: Format([tblNameHere].[stmtdate],"dd/mm/yyyy")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I've been successful in using aliases which are the same as the
underlying field name by fully qualifying the table and field in the
alias statement. I don't see the table name in the OP's code, so this is
a just a dummy, wher 'tblNameHere represents the table in which the field
stmtdate appears.

stmtdate: Format([tblNameHere].[stmtdate],("dd/mm/yyyy")"

Rather than

stmtdate:Format([stmtdate],("dd/mm/yyyy")"

you need

Format([stmtdate],"dd/mm/yyyy") AS statedate,

(As far as I'm aware, you cannot name the alias the same as the original
field, hence statedate rather than stmtdate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Thank You So much.
I want when retrieve its show date only in sql select query how can?
example: Its my code
dt = dbcon.selectvalue("Select refno as [Ref No],cardno as [Card
No],cardname as [Card
Name],stmtdate:Format([stmtdate],("dd/mm/yyyy")",duedate as [Due
Date],totamtdue as [Total Amount],paidamt as [Paid Amount],bank as
[Bank],branch as [Branch], chqnodate as [ChqNo/Date], status as
[Status],
acctype as [Acct Type],dueperiod as [Periodical] from Reminder");//
where
duedate between '" + BasicDatePicker1.SelectedDate + "' and '" +
BasicDatePicker2.SelectedDate + "'");
here is i want retrieve with format Please tell

:

On Fri, 9 Jan 2009 21:56:01 -0800, Charless Mohan

I want to when retrieve data or store date in date/time datatype
field. How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days
and
fractions of a day (times). As such, a date/time ALWAYS contains a
time
portion. This may be zero, midnight, and such date/time values can be
treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time
portion") use
the Date() function as its default, or as the source of the date. To
enter a
specific date, just enter the date without a time portion -
#3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to
midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion,
such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any
stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

 

[Douglas J. Steele]

No prob! Both you & I, though, may have suggested sub-optimal solutions.

It would probably be better to use

stmtdate: DateValue([tblNameHere].[stmtdate])

and set the Format property of the field to dd/mm/yyyy. That way, you can
still do date arithmetic on the field, and sort it properly.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Your Format statement is wrong, though: you've got an extraneous opening
parenthesis, plus an extraneous double quote at the end.

stmtdate: Format([tblNameHere].[stmtdate],"dd/mm/yyyy")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I've been successful in using aliases which are the same as the
underlying field name by fully qualifying the table and field in the
alias statement. I don't see the table name in the OP's code, so this is
a just a dummy, wher 'tblNameHere represents the table in which the field
stmtdate appears.

stmtdate: Format([tblNameHere].[stmtdate],("dd/mm/yyyy")"

Rather than

stmtdate:Format([stmtdate],("dd/mm/yyyy")"

you need

Format([stmtdate],"dd/mm/yyyy") AS statedate,

(As far as I'm aware, you cannot name the alias the same as the original
field, hence statedate rather than stmtdate)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Thank You So much.
I want when retrieve its show date only in sql select query how can?
example: Its my code
dt = dbcon.selectvalue("Select refno as [Ref No],cardno as [Card
No],cardname as [Card
Name],stmtdate:Format([stmtdate],("dd/mm/yyyy")",duedate as [Due
Date],totamtdue as [Total Amount],paidamt as [Paid Amount],bank as
[Bank],branch as [Branch], chqnodate as [ChqNo/Date], status as
[Status],
acctype as [Acct Type],dueperiod as [Periodical] from Reminder");//
where
duedate between '" + BasicDatePicker1.SelectedDate + "' and '" +
BasicDatePicker2.SelectedDate + "'");
here is i want retrieve with format Please tell

:

On Fri, 9 Jan 2009 21:56:01 -0800, Charless Mohan

I want to when retrieve data or store date in date/time datatype
field. How
can i do?
Thanking You

A Date/Time field is actually a double float number, a count of days
and
fractions of a day (times). As such, a date/time ALWAYS contains a
time
portion. This may be zero, midnight, and such date/time values can be
treated
as "pure dates" if you wish.

To set a Date/Time field to today's date at midnight ("no time
portion") use
the Date() function as its default, or as the source of the date. To
enter a
specific date, just enter the date without a time portion -
#3/15/2008#.

If a table contains dates with time portions already stored, you can
(permanently and irrevokably) erase the time portion and set it to
midnight by
updating the field to Datevalue([fieldname]).

To display only the date portion use a Format without a time portion,
such as
Short Date or "mm/dd/yyyy".

To search for records with a date/time on a specific day, ignoring any
stored
time portion, use a criterion such as

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])