הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Tuesday, February 21, 2012 10:35 AM
I am using VB 2008 & Access Database
for one of my DateTime field in data table i need to Remove it's Time part permently. I have tried following code but Syntax error received.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cmdText As String = "UPDATE GOODSRECEIPT SET DATE = CAST(DATE AS DATE)" 'CONVERT(CHAR(10), DATE, 103)" Dim cmd As New OleDbCommand(cmdText, cnSANDEEP) cnSANDEEP.Open() Dim I As Integer = cmd.ExecuteNonQuery MsgBox(I.ToString & " : RECORDS AFFECTED") cnSANDEEP.Close() End Sub
SANDEEP
All replies (10)
Tuesday, February 21, 2012 1:05 PM ✅Answered
Unfortunatelly, Microsoft still hasn't succeeded in offering a complete Access SQL reference. I was searching for it for half an hour, once again. So, it's not clear which expressions are allowed in a SELECT or UPDATE statement. Never mind... from memory, try this:
...Set Date = Cdate(int(date))
(Although this is not really a VB related question.)
Armin
Tuesday, February 21, 2012 12:52 PM
It's a DateTime field... you can't permanently remove it. At best you can set the time to midnight. Also you shouldn't use Reverved/Keywords as the names of your fields. Your use of DATE as a field name is going to cause confusion... Two ways around that: 1) change the field name (recommended). 2) put object identifiers around it [DATE] <- like that.
Tuesday, February 21, 2012 1:17 PM
Sandeep,
In addition to the others, with an MS Access DataBase is mostly used the Jet one, but there can be used in MS Access also be used for instance Sql server or the Accdb one (office 2007 and newer).
SQL Server, Accdb and Jet don't have columns which allow the date and time to be stored seperatly.
So it is also impossible to remove the time part, a datetime without a datetime setting is a date which is set as 00:00:00.
Success
Cor
Tuesday, February 21, 2012 2:00 PM
in VB, MyDate = DateValue(mydatetime)
as in 'MyDateStamp = DateValue(Now)'
In SQL, CONVERT(Date, MyDateTimeField) AS MyDate
as in 'SELECT SUSER_NAME() AS UserID, CONVERT(Date, CURRENT_TIMESTAMP) AS DateStamp'
Dan Kirk
OH, forgot, you want to display the date as date, in Access, you could create a text field and populate it with
UPDATE mytable SET mydate = CONVERT(Date, MyDateTimeField)
That will give you a row for populating, or you can do it through queries. Instead of populating your data set through the table itself, try something like
SELECT (CONVERT(Date, MyDateTimeField)) AS MyDate, mytransaction_idx, mytransactiondescription FROM mytable
Then you just call it as select * from myqueryname where mydate = CAST(mydatestring AS DATE)
Tuesday, February 21, 2012 2:25 PM
Armin, see below:
http://office.microsoft.com/en-us/access-help/CH006252688.aspx
http://support.microsoft.com/kb/239482
Paul ~~~~ Microsoft MVP (Visual Basic)
Tuesday, February 21, 2012 2:44 PM
Paul, that was my starting point. However, from a complete description I expect something like the Transact-SQL reference, which is examplary.
For example, in the Access Sql reference, the description of the SELECT statement
SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] ............
does not even mention that we can use an expression like "Int(field)" as a column in the result set instead of table.field.
But never mind, it was only a side note.
Armin
Tuesday, February 21, 2012 3:04 PM
Hello Armin
Thanks for the trick
i tried to the above function earlier as CDate(Date) which didn't served me, however the 'int' function you used set it all right.
however I didn't understand the use of int function here!
please clear me why is it used
SANDEEP
Tuesday, February 21, 2012 3:08 PM
hello Anderson
i will take care of it in future.
SANDEEP
Tuesday, February 21, 2012 3:31 PM
Hello Armin
Thanks for the trick
i tried to the above function earlier as CDate(Date) which didn't served me, however the 'int' function you used set it all right.
however I didn't understand the use of int function here!
please clear me why is it used
SANDEEP
A DateTime value is internally stored as a Double value. The integer part are the days since 12/30/1899. The fractional part is the time of the day (0.5 is high noon, 0.75 is 6 PM, etc). So, if you cut the fractional part using the Int() function, the time is "cut", or as correctly said by others already, set to midnight.
Access (SQL) is not as strict in type checking as VB is. In other words, it does implicit conversions if necessary, therefore you can use the Int() function and pass a DateTime value.
Armin
Tuesday, February 21, 2012 3:41 PM
BTW, C. Anderson is correct. There will always be both a date and time for the Date data type. It is only upon display (and formatting) of the value that the date or time can be removed.
Paul ~~~~ Microsoft MVP (Visual Basic)