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.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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??
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
In Query How Can I Avoid Date Field Without Grouping Whereas I Need To Group Other Fields!
Hai,
The below is my query, its executing correctly.
Code:rst.Open "Select ProdGroup,RcvdDate as d1,RcvdDate as d1,StkCode as Item,StkName as IName,Sum(TotValue) as t1,Sum(TotValue) as t2,Sum(TotValue) as t3,Sum(TotValue) as t4,Sum(TotValue) as t5,Sum(TotValue) as t6,Sum(TotValue) as t7,Sum(TotValue) as t8,sum(TotValue) as t9 from StkAgingView where ProdGroup='" & frmProductqueryrpt.Text1.Text & "' Group By StkCode,StkName,ProdGroup,RcvdDate order by StkCode", cnn, adOpenStatic, adLockReadOnly, adCmdText
For this query i am getting the output as below
____________________________________________________________
Item NumofDays <=7DaysAmount 8-15DaysAmount 16-30daysA
------------------------------------------------------------------------------------
001A 5 1500
001A 12 2000
RcvdDate is the RecievedDate field (RcvdDate - Date) by calulating this I am getting the Numofdays column and i am placing this in the MSHFlexgrid. I can't avoid the Numofdays field. by this I am getting the other column values. Due to this group by I also need to group the Date.
Actually I don't need to group the Date field.
I need the output as below. How can i get the below output in the MSHfexgrid.
Item NumofDays <=7DaysAmount 8-15DaysAmount 16-30daysA
------------------------------------------------------------------------------------
001A 7 1500 2000
The below code show how iam getting the NumofDays Column in the MshFlexgrid.
If MSHFlexGrid1.TextMatrix(i, 3) > 7 And MSHFlexGrid1.TextMatrix(i, 3) <= 15 Then
MSHFlexGrid1.TextMatrix(i, 7) = Format(MSHFlexGrid1.TextMatrix(i, 7), "#,###.00")
Else
MSHFlexGrid1.TextMatrix(i, 7) = 0
End If
Kindly suggest me or correct me how to code and the way which i am going is correct or not.
I try to draw the table here it is no clear, so I attached the picture file how i am getting the output, so by seeing this you can know what i am doing and what i am getting know. The highlighted rows in the picture has the same ItemCode.
Thank you very much,
Chock.
itchocks@rediffmail.com
<HTML>
<B><font color="#008000">HAVE A NICE DAY !</font></B>
<HTML>
Edited by - chs on 7/5/2003 11:34:21 PM
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 ?
SQL Update Subtracting Date From Date --> Result Into Field
Am having a little trouble with an Update SQL statement.
The code that I have come up with is
strSql = "Update Client_Details SET Client_Details.Days_CTS= " & Date & "- ClientDCTS.M_Date ........
It will come up with the error
Disallowed implicit conversion from data type smalldatetime to data type, table FOXGUI.dbo.Client_Details', column 'Days_CTS', Use the CONVERT function to run this query
The fields data types are as follows
M_Date smalldatetime
days_CTS Int
I can understand what they mean but I just don;t know the code that I should be using.
ANyone have any ideas?
Updating Date Field With Null Value (date Picker)
Hello,
I am using VB and access. For date I am nsing date Picker. How can I update a date with null value?
I am using the code update the date field:
DateColumn=#" & Format(DTP1, "mm/dd/yyyy") & "#
When I try to udate the field with null value I receive error: syntex error in date in query expression '##'.
Please advice me how to update with null values.
Regards,
Elahi
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!
DATE Problems - Change Date Format To European, Get Current Date, Subtract 2 Dates?
Need someone to point me in the right direction/start me off with this please as I am quite clueless on it at present...
I need something that can successfully subtract two dates...
eg. calculate the difference between the current date
02/04/04 (2nd April 04) and 29/03/04 (28th March 04)
...
which will then output a result of 5 days
So what I need to know is...
1.
I need to know how to get the current date from the system clock
2.
I must somehow get my date function converted into European date format (as at present it is in USA format ie. 2nd April 04 is 04/02/04 like April 2nd 04 which is not good!)
3.
and then I must actually SUBTRACT the 2 dates (the current date and a date that is given) from each other to give an integer which I can then use (ie. a value like 5 or 15).
But I have little idea how to go about this
Can anyone please start me off?
DATE Problems - Change Date Format To European, Get Current Date, Subtract 2 Dates?
Need someone to point me in the right direction/start me off with this please as I am quite clueless on it at present...
I need something that can successfully subtract two dates...
eg. calculate the difference between the current date
02/04/04 (2nd April 04) and 29/03/04 (28th March 04)
...
which will then output a result of 5 days
So what I need to know is...
1.
I need to know how to get the current date from the system clock
2.
I must somehow get my date function converted into European date format (as at present it is in USA format ie. 2nd April 04 is 04/02/04 like April 2nd 04 which is not good!)
3.
and then I must actually SUBTRACT the 2 dates (the current date and a date that is given) from each other to give an integer which I can then use (ie. a value like 5 or 15).
But I have little idea how to go about this
Can anyone please start me off?
Getting Numbers In Date Field Into Date Format???
in my access db i have an old table and the dates were never formatted eg its just 791023 as in 23 of oct 1979 so i had to change that to get it in date format so i did this using a query
UPDATE history_date SET history_date.theDate = format([theDate]," mm/dd/yy");
like it worked and put in the slashes but the date is different
it came out 01/25/55 when the date was originally 641027
why is it changing it ???
like i tried diff format like yy/mm/dd and still the same...
confused!!please help if u know anything...
Date Time Picker Control: How Do Set The Displayed Date To Null?
I have a front end database form with date fields using the MS dtp control. The fields are unbound, but populated using a data class. The problem is that on many occasions the date field is Null, ie no data available. On display the dtp control always defaults to a set date, usually today (this is a property function of the control). What I can't seem to do is to display no date, or to delete the date displayed. Any thoughts would be very gratefully recieved. API perhaps?
I'm trying very hard not to have to write an Active-X control based on the dtp (mainly because of multilingual support and different localisation issues regarding dd/mm/yyyy (used by 90% of the world's population, and mm/dd/yyy - used by the remaining 10% in the US - sorry just had to get that jibe in for those of us who despair at having to go in to custom formatting in Excel simply to get dd/mm/yy )
Many thanks
Date & Time Values In Date And String Format Differ
The program (debug) output below lists the last modification time of two files. File 1 is stored on a NTFS medium, file 2 sits on a FAT medium.
In order to be able to compare these dates, I need to round the odd seconds of the NTFS file up (FAT files have only even second values).
The lines after the file headers show the date & time value before and after the rounding, in date, double precision and string format.
file 1 (NTFS)
= 2005-07-12 08:57:09 = 38545,3730208333 = 2005-07-12 08:57:09
after rounding up
= 2005-07-12 08:57:10 = 38545,3730324074 = 2005-07-12 08:57:10
file 2 (FAT)
= 2005-07-12 08:57:10 = 38545,3730324074 = 2005-07-12 08:57:10
The strange thing is that when comparing these values, they appear to be different (!) both in date and double data format, not in string format.
2005-07-12 08:57:10 > 2005-07-12 08:57:10 (date format)
38545,3730324074 > 38545,3730324074 (double precision format)
2005-07-12 08:57:10 = 2005-07-12 08:57:10 (string format)
Any idea where this difference comes from?
Rounding up was done with DateAdd.
VB version 6, on XP.
Boiled down code has been attached.
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
Tracking Of Date Time Without System Date
Hello again
Yeah am back with a q again ....
I need to keep track of the date and time without using the system date as that can easily be changed and with the change of date my software will hinder a lot .......
Harris Moin
Diplay No Date In The Date Time Picker
anyone know how to display no date or have the date time picker come up with no date in it when starting up a program. My program comes up with fields displayed but blank and disabled. I would like to use the time picker and have the field come up disabled and blank.
Any ideas??????
DATE Problem - Formatting US Date MM/DD/YY To European Date Format DD/MM/YY
I have a problem whereby the user must enter a date into a field...
The system changes the date format to US standard which is MM/DD/YY
but I need to change the format to DD/MM/YY (European standard)
I have done this using the Format function, but I still have a slight problem
Format(mydate,"dd/mm/yy")
doesnt account for all dates...
for example...
if I enter 31/03/04 this is fine, it will accept it as that
However, if I enter 02/04/04 (todays date - 2nd April 04), it will change it to 04/02/04
Any ideas how I can counter this from happening?
Query With Date Ranges And Return Only Closest Date To 'to Date'
I am using an access db...
I am trying to pull back due dates for credit reports for clients. I have 4 tables I am working with for this query...
[Client_info]
[Dispute_track_eq]
[Dispute_track_exp]
[Dispute_track_trans]
[Client_info] has the basic client information....ID, SSN, name, telephone_numbers, yadd yadda..
each of the other tables hold the fields I am pulling from(other fields as well but these are the ones i need)...
[Dispute_track_eq].eq_due_date
[Dispute_track_exp].exp_due_date
[Dispute_track_trans].trans_due_date
Here is my problem...
I have variable that are set by dtpickers...'from_date' and 'to_date'. I am trying to get back each of these due dates that fall between the ranges set along with the [client_info] data linked by the SSN field. The problem is that sometimes these clients will have multiple due_dates(multiple rows) between the range. What the end result needs to be is this, I need to only have one record to come back with one of each due_date from each table, but only give me back the closest due_date to the 'to_date'(set by dtpicker) as well as the data from the [Client_info] table. And if there are no records for one or two but not the thirs field, an empty fields is ok(using a datagrid). But I can only pull back one row with the [client_info] fields, and one of each of the 3 due date fields. I have a query already but it doesnt even come close to working, it gives me many duplicates and doesnt give me the closest due_date to the 'to_date'.
Here is my query, but it doesnt work. Any help would be greatly appreciated, I am in dire need of help on this one.
Code:Private Sub Command4_Click() '***Due dates report grid date
to_date = dt2.Value
from_date = dt1.Value
If from_date = vbNull Then
MsgBox "Please select a FROM DATE"
Exit Sub
ElseIf to_date = vbNull Then
MsgBox "Please select a TO DATE"
Exit Sub
Else
Dim ID_new As String
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim ID_pass As String
Dim active As String
active = "Active-Negotiations"
Set rs = New ADODB.Recordset
strSQL = "Select" & vbNewLine
strSQL = strSQL & " [client_info].[FirstName], [client_info].[LastName], [client_info].[HomePhone], [client_info].[MobilePhone], [client_info].[Client_status], [client_info].[Category], [client_info].[Appt_notes],[client_info].[Appt_date],[client_info].[Appt_date_time], " & vbNewLine
strSQL = strSQL & " [dispute_track_eq].[eq_due_date], [dispute_track_exp].[exp_due_date], [dispute_track_trans].[trans_due_date]" & vbNewLine
strSQL = strSQL & " FROM (dispute_track_exp INNER JOIN (dispute_track_eq INNER JOIN client_info ON [dispute_track_eq].[SSN] =[client_info].[SSN]) ON [dispute_track_exp].[SSN] =[client_info].[SSN]) INNER JOIN" & vbNewLine
strSQL = strSQL & " dispute_track_trans ON [client_info].[SSN] =[dispute_track_trans].[SSN]" & vbNewLine
strSQL = strSQL & " Where (dispute_track_trans.trans_due_date > #" & from_date & "# And" & vbNewLine
strSQL = strSQL & " dispute_track_trans.trans_due_date < #" & to_date & "#)" & vbNewLine
strSQL = strSQL & " OR (dispute_track_eq.eq_due_date > #" & from_date & "# And" & vbNewLine
strSQL = strSQL & " dispute_track_eq.eq_due_date < #" & to_date & "#)" & vbNewLine
strSQL = strSQL & " OR (dispute_track_exp.exp_due_date > #" & from_date & "# And" & vbNewLine
strSQL = strSQL & " dispute_track_exp.exp_due_date < #" & to_date & "#) AND NOT NULL"
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source= " & db & " "
.open
End With
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Source = strSQL
.open
End With
If rs.RecordCount = 0 Then
MsgBox "Sorry, no one matched that status, try again", vbOKOnly, "Credit Matrix"
Set DataGrid1.DataSource = rs
Else
Set DataGrid1.DataSource = rs
End If
End If
I would be forever in your debt for helping me as I am almost out of hair....FROM PULLING IT OUT!!
Edited by - ronmegga on 4/1/2004 5:26:03 PM
DATE Problem - Formatting US Date MM/DD/YY To European Date Format DD/MM/YY
I have a problem whereby the user must enter a date into a field...
The system changes the date format to US standard which is MM/DD/YY
but I need to change the format to DD/MM/YY (European standard)
I have done this using the Format function, but I still have a slight problem
Format(mydate,"dd/mm/yy")
doesnt account for all dates...
for example...
if I enter 31/03/04 this is fine, it will accept it as that
However, if I enter 02/04/04 (todays date - 2nd April 04), it will change it to 04/02/04
Any ideas how I can counter this from happening?
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.
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
Addition To Date/time Field In Oracle
Hi Guyz,
Q1
Lets say i have the following data in the date time field..
6/25/2004 7:02:31 AM
How do i add another 8000seconds to this data in oracle?
Q2
And how do i search, for an example, give me all the list of data with 6/25/2005, no matter what time it is.
I tried using the like operator but it doesnt seem to work..
Any ideas?
thankx in advance
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?
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.
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
Server Date/time Instead Of Windows Date/time
Dear VB Experts,
We are developing a Client/Server application whereby the VB executables will reside at the PC.
Server run on HP-UX 10.20. Database is Informix-Online.
One of our task is to utilise the server date and time for timestamp instead of using the Windows date and time.
How do we do that ?
Thanks in advance.
RWS
|
TRACKER
