Visual Basic :: Addition To Date/time Field In Oracle

Put A Date Into An Oracle Table's Field?
I had a project that was using SQL, and then had to modify it to put a date into a field, going from MS Access to Oracle. Here is what the code looked like:

strSQL = ""
strSQL = strSQL & "INSERT INTO MasterPN "
strSQL = strSQL & "(PN, Description, FSCM, "
strSQL = strSQL & "ATAPN, ESDS, SPN, "
strSQL = strSQL & "Keyword, CompVal, DimData, ModUser, ModDate) "
strSQL = strSQL & "VALUES ('" & rsTemp!PN & "', '" & strDescription & "', '" & rsTemp!Fscm & "', "
strSQL = strSQL & "'" & strATAPN & "', " & intESDS & ", '" & strSPN & "', "
strSQL = strSQL & "'" & rsTemp!Keyword & "', "
strSQL = strSQL & "'" & rsTemp!CompVal & "', '" & rsTemp!DimData & "', "
strSQL = strSQL & "'SYSTEM'" & Now()
conOracleDb.Execute strSQL, , adExecuteNoRecords

This did not place a date into the desired field. So, I deleted that piece of code and put in the following code:

strSQL = ""
strSQL = "SELECT * From MasterPN"
rsPart.Open strSQL, conOracleDb, adOpenStatic, adLockOptimistic, adCmdText
If Not (rsTemp.EOF = True And rsTemp.BOF = True) Then
rsPart.MoveLast
rsPart.AddNew
rsPart!PN = rsTemp!PN
rsPart!Description = rsTemp!Description
rsPart!Fscm = rsTemp!Fscm
rsPart!ATAPN = rsTemp!ATAPN
rsPart!ESDS = rsTemp!ESDS
rsPart!SPN = rsTemp!SPN
rsPart!Keyword = rsTemp!Keyword
rsPart!CompVal = rsTemp!CompVal
rsPart!DimData = rsTemp!DimData
rsPart!ModUser = "SYSTEM"
rsPart!ModDate = Now()
rsPart.Update
End If

And that code worked!! My question...... can anyone tell me why one way doesn't work but another way using the same Now() function does work? This one drove me nuts for the better part of a day.

Are there issues between MS Access date functions and date items in Oracle?

Any help and advice on this will be greatly appreciated. Thanks!!

Added green "resolved" checkmark - Hack

How To Insert A Date Field In Oracle Table
I am using VB5 and oracle as backend I want to insert a date value in a date field of a table. what insert statement shoud i use??
i tried number of permutations.
e.g.
sqlstr = "insert into customerqry(cid,Billing_End_Date) values(1, " & Format("20/11/1999", "short date") & ")"
it is giving me errror.
please anyone help me. it is a bit urgent.

thyanx in advance

Grouping Records By Month In Oracle PL/SQL From DATE Field
Hi,

I'm sorry if this was posted before. I searched around and didn't see anything that answers my question. So if this is a repeat, sorry.

I have an Oracle table with a DURATION column and a DATE column. The dates are stored as '1/1/0001 12:00:00 AM' format and the durations are stored as Doubles. I'm trying to write a query that returns the SUM of the duration GROUP BY month. So far, here's what I've got:

Code:SELECT DATE, SUM(DURATION) AS DUR
FROM table
WHERE DATE BETWEEN TO_DATE('1/1/2000', 'MM/DD/YYYY') AND TO_DATE('12/31/2000', 'MM/DD/YYYY')
GROUP BY TO_DATE(DATE, 'MONTH, YYYY')

This version doesn't seem to work. If anyone can help straighten this out I'd really appreciate it!

-DDennison

Grouping By Date Where Date Field Is Date/time
I have an SQL7 database storing datetime data, and totaliser reading, new records are added every 5 minutes.

I have written the following sql to give me the the min and max totaliser reading for each day - which works fine - except that the resultant Date field is a text field not a date field so the order statement generates an incorrect listing if the data selected sarts in one month and ends in the next, eg.

select left(Date, 12) as Date, Min(Reading) as Day_Start, Max(Reading) as Day_End from Steam where tag_no = 2 and (Date <= '24-November-2005 00:00:00' and Date >= '22-October-2005 00:00:00') Group By left(Date, 12) order by Date asc

Is there another function I can use to extract just the date from the date/time field as a date data type?

I cannot alter the database as its structure is predetermined by the remote field device supplying the data.

Oracle Date Time
Hello all,

I am trying to insert user input (Date/Time) into a date/time field in an oracle table. I tried many ways to format the inserted value but i keep getting an oracle error. Would any one help me with the how to formate date/time to insert into date/time field in oracle through an insert sql statement. The user could pick the date and time they want to insert into this field.

Thank you so much in advance!

Oracle Server Date/Time
I want to use the oracle server machine date and time while user save any transactions from the client machine instead of the client machine date and time.

How i can solve this problem?

Thanks in advance

Only Want Date Part Of Date Time Field
How can I using an SQL statement just reitrieve the date part of a database field.

The data is stored as 10 05 2001 12:31.

At moment sql is bringing back the whole field. For grouping purposes I only want the date ie 10 05 2003.

Any ideas??

Date Time Field
I have a table field that store date and time in the format of yyyy/mm/dd hh:mm:ss, how can i select the date only ?

Date & Time As Field Name
Hi all,

I have an application that reads the dbf file for data input, the application is working fine, except for some reasons it stops responding , means fails to read the dbf file.

The said dbf file contains two fields as Date & Time , the vb applications reads this fields first, is this the reason for system not responding.

Regards

JOhn

Date Being Put Into Field As Time
Hi,

I'm trying to add a date value to a field in access using SQL but it's showing up as a time.

Both the table and date field are being created at run-time.

The code used to create the "dDate" field is:
"ALTAR TABLE Table1 ADD COLUMN dDate DATE"
- This seems to create a Date/Time field just like any other.

The code I used to add a date value is
"UPDATE Table1 SET dDate = " & "01/01/2006"

I've also tried a date variable and a string variable formatting both as dates, not formatting either ...  No matter what I do, it just inputs a time value.

All this is being done from outside of Access using VB & SQL.

Any comments and/or instructions would be greatly appreciated.  Thanks in advance.

Paolo

Add X Days To Date/time Field
Hello

I have a texbox (txtReceivedDate) that is populated from my MS Access Database Field receivedDate that is set to date/time.

What I am trying to do is add 10 days to the receivedDate and have that NEW date appear in my txtbox (txtNewDate)

Example: txtReceivedDate would be 7/28/2004 and the txtNewDate would be
8/07/2004

Any help with this would be appreciated

Tks
Bill

ADO Changing Time And Date Field???
How can I update a "DateTime" field in table.dbf with a date time value that does not take on the format that is set in the Windows "Regional Settings"

My Regional Settings are for short Date yy/MM/dd

My code is a follows:
Dim rs As adodb.Recordset
Set rs = New Recordset

rs.Open "Select * From table.dbf", "filedsn=mydsn;", adOpenDynamic, adLockOptimistic

rs.AddNew
rs!Timestamp = Format(Now, "mm/dd/yy")
rs.Update
rs.Close

When I look in table.dfb the date is stored as yy/MM/dd not as mm/dd/yy

Thanks in advance

Jeremy

How To Detect A Date/Time Field ?
I need to know how to determine a Date/Time field from an Access database, rs!inputdate is a Date/Time field, I use RS.ADDNEW to create this record but I did not write anything in it. But following is not working:

rs!inputdate = ""
rs!inputdate = Null
rs!inputdate = 0
rs!inputdate = vbNullString

Please help .....

How To Erase Date And Time Field
I have 2 fields defined as DAT/TIME. Returned_on has a DATE and Returned_at has TIME. How can I erase them thru the application?

I am using Access data base in my VB6 application.

UPDATE Contracts SET Returned_on = XX, Returned_at = YY WHERE Cont_id = 1

What should I set my XX and YY to.

Date And Time In A Single Field
In a SQL table what is the format to insert the date & time in single datetime field through VB 6.0

Filling Date/Time Field With NULL
Shalom,

I have a Date/Time field in a Microsoft Access 2000 database and I need to fill it will nothing (NULL). Here is my current statement:

Code:
gtSQLStmt = "UPDATE TICKET_DETAILS SET" & _
" sched_dte = '" & Null & "', " & _
" alert_user = 0" & _
" WHERE index_num = " & Val(txtIndexNum)

StdLibDB.execute_sql_sub gtSQLStmt, adoConA
When I execute this code I get the following error:

"Data type mismatch in criteria expression"

(this is on the execute_sql_sub line which executes my sql of course)

How can I put a NULL or nothing in that Date/Time field?

GrayKnight

Clear ACCESS Date/time Field
The following code is a test to set a date/time field in ACCESS to a date and then try to set it back to a blank (clear the field). Setting the field to a valid date works ok, but when I try to clear out the field, I get a "Type Mismatch" error.

Does anyone know how to blank out a date time field from VB?

Code:
Private Sub Command_Click()
Set cndb = New ADODB.Connection

cndb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:master.mdb;Persist Security Info=False"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Shiplist WHERE [Order ID] = 'BL00004'", cndb, adOpenDynamic, adLockBatchOptimistic, adCmdText

If rs.EOF Then

Else
Debug.Print rs![Expected Ship Date]
rs![Expected Ship Date] = "9/2/2005"
rs.UpdateBatch

Debug.Print rs![Expected Ship Date]
'the next line will cause a "Type Mismatch" error
rs![Expected Ship Date] = "" 'trying to set the date/time field to a blank
rs.UpdateBatch

End If

rs.Close

Set rs = Nothing

cndb.Close

Set cndb = Nothing
End Sub
tia,
flynn

Date/Time Field - Select Returning Nothing
Hi all,

I have a vb app with an access 2000 db. The problem is that I have a field in the db called DateAtt where the type is Date/time (shortdate - dd/mm/yy). When I try to select values from this field vb returns a recordcount of 0.

I understand that you can enclose the date within 2#'s (date literal), but when I do this the recordcount is 0 even though the date entered exists in the db. This is because eg. the record 14/03/04 (in the db - dd/mm/yy) <> 03/14/04 (from the sql query mm/dd/yy).

sql = "Select DateAtt from Att where DateAtt = #" & dateEntered.text & "#"

Is there a way of getting around this problem and could you provide the sql for this? Could you use the date type instead of literals?

Also is there a way to select a date where the day is not known therefor bring back all records which have a month 03 and year 04. So in other words is there a way to put the % infront of the date selected (in this case just being the month and year). So it would look like %/03/04?

Thanks in advance!

Searching An Access Date/Time Field?
Good Morning.
I am having trouble searching an Access(2000) DataTime Field.

The field holds both the Date and Time: But I only want to search this field by the Date.

Below is my search string it returns no records. (Does not give an error, just no records.)

searchsql = "select * from slog where wdate=" & Chr$(35) & FormatDateTime(DTPicker2.Value, vbShortDate) & Chr$(35)

Select Statement With A Date/Time Field
Howdy!

I want to pull all records from earlier than 2 two days ago.
I am using Access97,with a Date/Time field, but I can not get the thing to work.

Here's what I have

Dim TimeLapse
Dim SQL

TimeLapse = Now - 2

Set adoMasterRS = New Recordset

SQL = "Select * from Master where RowUpdate >= '" & TimeLapse

adoMasterRS.Open SQL, db, adOpenStatic, adLockOptimistic

Thanx

Insertin Date And Time On Datetime Field
Hi everyone,

Do you guys know how to insert date and time values on a single SQL Server datetime field using ASP or VB?
I've tried using Convert but it doesn't seem to work... I always get error messages.

Thanks a lot.

Otavio

Comparing Date Time Stamps Field
I have a date time stamp field in my database. And i need to count calls for one day. So my sql statement looks something like this :
Code:
                Dim bdwsmtCon As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};server=192.168.2.5;port=3306;database=bdwsmt;pwd=knicks24;uid=tmstms;")
                Dim bdwsmtCommande As New Odbc.OdbcCommand
                Dim bdwsmtReader As Odbc.OdbcDataReader
                Dim strSQL As String 'Select statment
                Dim curDate As String 'curDate
                Dim endDate As String 'end string for sql statment
                curDate = startDateTimePicker.Value.AddDays(dblStartDay).ToString("M/d/yyyy")
                endDate = startDateTimePicker.Value.AddDays(dblEndDay).ToString("M/d/yyyy")
                    strSQL = "SELECT Count(*) AS MyCount FROM wab_answers WHERE answerTime > #" & endDate & " 11:59:59 PM# AND answerTime < #" & curDate & " 11:59:59 PM# AND wabScreen=1;"
                    MsgBox(strSQL)
                    bdwsmtCommande = New Odbc.OdbcCommand(strSQL, bdwsmtCon)
                    bdwsmtCommande.Connection.Open()
                    bdwsmtReader = bdwsmtCommande.ExecuteReader(CommandBehavior.CloseConnection)

The error is the # # round my date and time. I tried to use ' ' around them but that just gave me 0 for the count. Any Ideas?

How To Insert In A Date/Time Field In MS Access
I always get the error: "Invalid operand for operator: <assignment>"

I tried '1997-01-01' or '19970101' or '01011997' or '01-01-1997' .. and many others with "/"

Nothing works ...

Ado (command) Updating Date/time Field
Hi,
I'm trying to update a field of type date/time to null value using sql query.

my sql query:
"Update jobs Set mydate.value = DBNull" ....

This doesn't work!
What do I need in this sql statement so that I can update date/time field with nothing (empty).
Thanks.

Activex Control For Date/Time Field
Hi,

Can anybody suggest me an activex control accepting Date and Time. For Date i found many Callendar controls(eg. sheridan) but i need a similar control for Time. All suggestions are welcome.

Thanks in Advance

Chandu

Returning A Date/Time Field To Empty
I am accessing a database in the following manner:

set db = OpenDatabase("c:Databasename.mbd", false)
set rec = db.openrecordset("tblName",dbOpenDynaset)
rec.MoveFirst
Do Until rec.eof
With rec
.Field("date")=vbNull
.MoveNext
End With
Loop

It continues to fill the date with "12/31/1899"

I can't figure out how to just delete the information in the field so that it is empty.

Jason Hatt

Problem Storing Date/Time In TEXT Field.
Hi,

I have an Access Table with a field called "VisitDate" and is a Text Field of size 50. I want to store current Date and Time in this field.

My program stores current date and time in a variable "ProgramDate" which is of Date DataType

I tried following code, but it generates this error:

Run-time error '-2147217887 (80040e21)'
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Code:
Dim myDateTime As String

myDateTime = CStr(ProgramDate) + " " + CStr(Time)

rsTempReport!VisitDate = myDateTime
I want to store data in this format, e.g: 23/9/2005 11:15 AM

Create Date/Time Field And Loading Default Value
I am trying to add some fields programmatically to a database using DAO. I need to load some default value to the date/time fields in order that they ar not null. Here is what I tried:

xs = ""
On Error Resume Next
xs = db.TableDefs("Department").Fields("begHH").Name
On Error GoTo 0
If xs <> "reciprocal" Then
db.TableDefs("Department").Fields.Append _
db.TableDefs("Department").CreateField("begHH", dbDate / Time)
db.TableDefs("Department").Fields("begHH").DefaultValue = "12:00 AM"
'db.Execute "UPDATE Department SET begHH= "12:00 AM" WHERE
begHH IS NULL", dbFailOnError
End If

It does not like the string of "12:00 AM" at all. Does anyone know what value I should put in the newly created fields. Thanks fo rthe help.

Store Date And Time In SQL Server Datetime Field
I wanted to store date and time (both in single field)
I tried following in order to store the date entered in text box in format dd/mm/yyyy

Please help

Thanks in advance
VIkAS

Excel Won't Accept Date Time Field From SQL Server
Hi

I'm workin on a project at the moment that requires that I can write the contents of a datagrid to an excell work book, I have most of the code sorted however excel does not show the date time fields pulled from the SQL server. Here is my code

Private Sub btn_dumptoexcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_dumptoexcel.Click
        Try
            'Variables
            Dim ExcelApp As New Excel.Application
            Dim Wkbook As Excel.Workbook
            Dim Wksheet As Excel.Worksheet
            'Make the Excel Application visible
            ExcelApp.Visible = True

            'Get a new workbook and select the first sheet
            Wkbook = ExcelApp.Workbooks.Add
            Wksheet = ExcelApp.ActiveSheet

            Dim dt As DataTable = ds.Tables(dsTableName) 'Dataset name used to populate the datagrid

            Dim col As DataColumn
            Dim n As Integer

            'Add field names to each cell
            For n = 0 To dt.Columns.Count - 1
                Wksheet.Cells(1, n + 1).value = dt.Columns(n).ColumnName
            Next

            'Loop through the rows in the table and put the data into their cells
            Dim row As DataRelation
            For n = 0 To dt.Rows.Count - 1
                Wksheet.Range("A" & n + 2, "G" & n + 2).Value = dt.Rows(n).ItemArray
            Next

            'Format field names as bold and center
            With Wksheet.Range("A1", "L1")
                .Font.Bold = True
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .Font.Size = 12
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

       End Sub

Anyone got any ideas or see where I'm going wrong?

Thanx
Phil

Inserting NULL Value Into Date/time Field - Sybase -
i cannot insert a null value into a date/time field in a sybase database

Code:
'STORED PROCEDURE
sybObj.CommandText = "Data_Put"

         acci_insert(0) = "Null" 'GET IMPLICIT CONVERSION ERRORS
         acci_insert(0) = "Null" 'TRIED Null, NULL, DBnull.value, "", '' - nothing works

        'EXECUTE STORED PROCEDURE
        sybObj.Execute , acci_insert

The field has been set to allow Null values.

Edited by - AbbydonKrafts on 3/20/2007 6:21:53 AM

Setting Label.Caption From A Date/Time Field Value
Hi,

I need to set a labe's caption to a date/time field from a SQL database. I have setup the connetion string and all and I define this variable in VB:

Dim dtDate As Date
dtDate = rsInfo.Fields("UploadDate").Value

The Field "Upload Date" looks like this:

4/27/2001 11:12:09 AM

But my label's caption show up like this:

12:00:00 AM

How can I set it correctly????? Please Help,

Herick

Date Addition
In VB 6.0 by adding one to today's date you can get tomorrows date.

ie. TomorrowDate = Today + 1
if Today = 11/17/2004 TomorrowDate will return 11/18/2004

How do I do this in VB.net?

Strings And Date Addition / Subtraction
Sigh, trying to figure out a more efficient solution to this issue. I have an application that only accepts dates in full yyyymmdd format (months / days must be zero padded i.e. Jan - "01", Dec - "12").

To get the system date I am currently using the following:
'Get Today's System Date
sDate = Day(Now)
lDate = len(sDate)
If lDate=1 Then
sDate = "0" & sDate
End If
sMonth = Month(Now)
lMonth = len(sMonth)
If lMonth=1 Then
sMonth = "0" & sMonth
End If
sYear = Year(Now)
sysDate = sYear & sMonth & sDate

Which seems to work alright.

My problem is that I also need to do date addition and subtraction using the datediff function. The only way I can think of is to take the sysDate generated above, perform a datediff on the date within, split the result out into component Year, month, and Day fields, pad the month and day values, then re-merge the values back into a YYYYMMDD format.

Is there an easier way than datediff - separate - format - remerge to accomplish the above?

Thanks!

Time Addition
I may sound stupid in asking this question
How do I add 2 minutes to any given time?

Time Addition
i posted a thread on adding two time periods to geather , and i have read on the datadiff function but still can' t figure out how to do what i want to do. i have a database, and on the form it has a textbox with a time (exp. 08:00) and on each record the time is different . i NEED to be able to add the hours and min up
(exp. record1 has a time of 08:00 and record2 has a time of 08:30 = total 16:30 )and i want to be able to press a button and it give me a total of all record times )

Time Addition And Subtraction
HI, I am using a funtion someone here told me to try and it works great. Here is the funtion:

Function fntimediff(ByVal To_1 As Date, ByVal From_1 As Date) As Double
fntimediff = Round(DateDiff("n", To_1, From_1) / 60, 2)
End Function

It gives me the difference between two times. Only problem is that after I get my results for say 11:30am (To_1) and 2:15pm(From_1), I get a result of 2.75 hours. How can I convert this time to be 2:45?

Let me know if you need more info.

Thanks

Sherriff

Problem With Addition Of Time
Ok I have a problem that seems so simple, but I just cant get it done:

I have 5 label controls, each with a time in it formatted in the H:mm format.... I simply want to add these all together and display the accumulated time in a 6th label control

This correctly formats the minutes into H:mm format and displays it in the lblDailyHours.Caption control:

'formats the hours and minutes into H:mm format'
lblDailyHours.Caption = Format((lblDailyHours.Caption 60) & ":" & (lblDailyHours.Caption Mod 60), "H:mm" )

but now, I need to add the number in the lblDailyHours.Caption (let's say it displayed 8:30 in the caption as an example), to another label control of a number in the same format and display that result in the H:mm format in another label control

REQ Help: Calculating Elapsed Time From Date/time Began From Date/time Ended
I have four controls, two are Date-Time Pickers set for Date and the last two are text boxes set for Time with the "...If Not IsTime()..." validation.

It is the standard Date and Time problem began, Date and Time problem ended.

I am trying to calculate the elapsed time between these in "" DAYS "" HOURS ""MINUTES.

I coded this in Lotus Notes yet exporting my knowledge to VB is problematic.

Has anyone a code snippet of how to do this in VB - yes, I know about the DateDiff.

Many, MANY thanks!

Addition Of New Rows In Data Grid At Run Time
hello,
i am developing an application in vb6.i used data grid in my form to populate it with the records of a table and also to add in more records and update the existing records.using text boxes i add records in it ........but when i add the record.....it is added in the database but it is not appeared in the datagrid at that time .but when i close the form and run it agian then it is listed in the datagrid.
how can i do this that when i add a new record it must be appear at that time in the datagrid.
plz help meThanks in advance
fareeha

Every Record Has A Date Field. I Want To Find A Date And From This Date, I Want To Read Records To E
Every record has a date field. I want to find a date and from this date, I want to read records to eof. What is the code? - Thanks

Date Conversion As Oracle Database Date Default Format
Select sysdate from dual ; //oracle database
The result is : 28-AUG-04

How to convert “date” in vb to the above format?

Using below statement :
FormatDateTime(Date, vbLongDate) // Saturday, August 28, 2004

Then, combine with string functions (such as instr, left, mid, etc) can be achieved the desired result.

Any shortcut than above mentioned?

Thanks.
vb999

How To Convert Oracle Date To Vb Date Type
how to convert an oracle date field
to a vb date type in this format: yyyy-mm-dd
and still maintain a date type?

Date String To Oracle Date Datatype
I have variable that holds a date string looking like this: '05312002140059'
[The format is read as: 'mm/dd/yyyy hh24:mi:ss']. I need to execute a stored procedure
in Oracle through VB. The date string comes from a tab delimited text file, which is
read into a string variable in VB. From there, I want to assign the value in the
string varible to the Command object's parameters. The error that I'm getting is
that the string's datatype is mismatched or invalid with the stored procedure's
data type. What am I doing wrong?!

Change Date Field To A Text Field Without The Slashes
I have a field called statement date which is a date type "08/21/2008".
I want to remove the "/" from that variable. Is there an easy way to do that?

How To Check If The Current Date/time Is >15 Mins More Than Another Date/time?
Say you have the date/time stored in a string like so:
"2008-06-05 01:58:30"

How can you check if the current date/time is > the date/time stored in the string + 15 minutes?

Date Picker Control Doesn't Show Current Date In Field
How do I set the display date in the Date Picker control field ? MSDN says that it should return the current date by default, but it only shows the current date highlighted in the drop down calendar.

The control shows the date when the control was created and added to the form.

How do I set the date at form load ?

Code:
dtpicker1.value = ? ' how do I call current date ?

ORACLE: Field Lenght!
Hello!
for the first time, i'm using ORACLE DB with my VB Project.
I find a little problem, with CHAR field:
1) i open my recordset
2) i set datasource and datafield of my textbox
3) the value the DB ORACLE have, after my digits, is a string of the dimension of the DB Field Size!
if i write: "HELLO"
i'll find in the field:"HELLO "

not very nice
any knows hot to store value dimensioned by my digits, not by field size?