C
Charless Mohan
I want to when retrieve data or store date in date/time datatype field. How
can i do?
Thanking You
can i do?
Thanking You
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
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:])
C
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 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
John W. Vinson said: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:])
C
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 when retrieve by sql select query its show date only.
how can that : select query format for date only retrieve.
John W. Vinson said: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:])
D
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)
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)
Charless Mohan said: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
John W. Vinson said: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:])
J
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
'====================================================
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
'====================================================
Charless said:Thanking You.
I want when retrieve by sql select query its show date only.
how can that : select query format for date only retrieve.
John W. Vinson said:A Date/Time field is actually a double float number, a count of days andI want to when retrieve data or store date in date/time datatype field. How
can i do?
Thanking You
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:])
G
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")"
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")"
Douglas J. Steele said: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)
Charless Mohan said: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
John W. Vinson said: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:])
D
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)
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)
George said: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")"
Douglas J. Steele said: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)
Charless Mohan said: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:])
G
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.
Thanks for the catch. I guess am too used to UA, where I get a chance to
edit before committing the final post.
Douglas J. Steele said: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)
George said: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")"
Douglas J. Steele said: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:])
D
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)
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)
Douglas J. Steele said: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)
George said: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")"
Douglas J. Steele said: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:])