See Related Forum Messages: Follow the Links Below to View Complete Thread
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.
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
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
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 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
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
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...
AutoNumber Field Vs. Date Field...?
I have a database with some data. This database has a date field. When I add an AutoNumber field my data become this:
Code:
DateField AutoNumberField
01/01/2004 1
01/01/2003 2
01/01/2002 3
But I'd like to do it, sorting by date:
Code:
DateField AutoNumberField
01/01/2004 3
01/01/2003 2
01/01/2002 1
How to do it?
Thanks.
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.
Microsoft Date Time Picker Bug - Resizes Every Time I Open The Form !
Hi All,
I've created and published a custom form in Outlook 2000 which includes 5 DTpicker controls.
Every time I open a new item using this form, the controls resize to a very small size. Subsequently when I close the item (with or without saving any changeS) and open the form in design mode, the controls have stayed in the small size.
ANy ideas what is causing this behaviour and how to solve it? Any help would be greatly appreciated - the ap is ready for deployment and this is the only thing I am not able to solve !
Cheers,
Kaus
Converting UNIX Time In Milliseconds To Normal Date Time Format?
Hi all. Dont know if this is common or not. Did a search, but found nothing.
I have got a 48 bit date and time value represented in hex being generated on a unix system. The 8 byte date and time is measured from epoch 1(1 Jan 1970, midnight) in milliseconds, ie. the Unix hex data is one extra long number in milliseconds.
I want to convert this to a readable format such as: mm/dd/yy hh:mm:ss.sss
I have some examples taken from some other program runing on Unix system:
Unix data - 7E E6 64 B7 FF 00 (no idea how to convert this to equivalent)
equivalent - 10/20/04 17:31:43.614
Unix data - 5D 2D 26 F5 00 01
equivalent - 12/21/04 10:22:27.677
Thanks.
Universal Coordinated Time UTC Seconds To Current Date And Time (Local)
Hi All. Never had to do this one, looking to see if some else has done it.
I haven't seen exactly what I need out on the net under free code
locations, but have come close.
I need to convert a LONG INTEGER of UTC Seconds ( universal coordinated
time UTC which is the number of seconds since midnight on January 1, 1970),
to a local date and time.
If anyone has written source to do this, and doesn't mind sharing, I'd love
to get a copy.
Thanks Much.
Bill
|
TRACKER
