Visual Basic :: Excel Won't Accept Date Time Field From SQL Server

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??

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

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

Defaulting A DateTime Field To Date Only In SQL Server Db
I have a datetime field "RecordDate" that I want to have default to the current date. If I specify getdate() as the default value I end up with the date and time both. In Access I was able to specify the field format as mm/dd/yyyy and the default value as Date(). Is there anything similar I can do in my SQL database to force the data to format the way I want it?

Vb-sql Server Date Field Problem:URGENT
in my vb application i am using DTPicker in forms.
when i am trying to insert datefield as dd/mm/yy format in my table
(sql server) through my vb application error is occured . the error is

error -2147217913 conversion of a char data type to a datetime data
type resulted in an out of datetime value

when i try to insert datefield on dd/mm/yyyy through query analyser there is
no problem

help me and send some soucecode to solve this problem in my vb application
its very urgent

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

SQL Server/Access 2007 Date Field Is Slooooow! Why?
I am working with a client to migrate an Access 2000 app to Access 2007. I went through the conversion process and everything runs great except anything having to do with date/time fields. The Access app is odbc-linked to a back-end SQL Server 2000 DB. Whenever a date field is clicked, the app appears to "hang" for about thirty seconds! This is driving me nuts! There is no event properties tied to the field. In fact, I can look at the table view and click on a date without even going through a form and it does the same thing. What the heck is it doing? How can I fix this? Any ideas?

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?

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.

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

Doubt In Fetching Time From Date Field From A Oracle Database Using VB 6.0
I'm using Oracle 9i as my database,in which I have a table with a Date field.Using VB 6.0 I want to fetch the time value from the date field in time format using a Select Query.

Server Date And Time
Hi all.

how can i get the server date and time. The server means the system where my access database kept and from there more than one user accessing the database. The whole user has to get the server machine's date and time. how can i get it?

saj

Get Time And Date From Server
There's something I can't do.

I wanna get the Time and Date from my Server (Windows 2000 Server), to change these values on others PC (where I use a programm of mine).

I wanna know if only using API's I can get these values from my Server or I have to generate a "Server Program" using socks (running in my Server) that give me this values when I connect to this "Server Programm".

Getting Date/Time From Server
How would I get the system Date and Time from a SQL Server?

Thanks

Get Server's Date && Time?
how can i retrieve the date and time value of a SQL server? by using ado

Quote:

ado.Open "select getdate() as ServerTime"

Get Only Date And Time In SQL Server
Hi,

I want to get only date and only time in SQL Server.

Thanks for help

Getting Date And Time From A Server
Is there anyway to get the date and time from a server?

Server's Date/Time
I am running my program in a workstation running Windows 98 as its OS. The server uses Windows NT 4. How can I extract the System's Date and Time from the server? Please help..Urgent

Get Time And Date From Server
i am trying to use API to get the time and date from server but i am getting "Can't find DLL entry Point NetRemoteTODD IN netapi32.dll" any suggestion why am i getting this error message
Here is the code I copied and paste this code from One of the thread from this web site

Private Declare Function NetRemoteTOD Lib "NETAPI32.DLL" _
(ByVal server As String, buffer As Any) As Long

Private Declare Function NetApiBufferFree Lib "NETAPI32.DLL" _
(ByVal buffer As Long) As Long

Private Declare Sub CopyMemory Lib "KERNEL32" Alias "RtlMoveMemory" _
(hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)

Private Type TIME_OF_DAY
t_elapsedt As Long
t_msecs As Long
t_hours As Long
t_mins As Long
t_secs As Long
t_hunds As Long
t_timezone As Long
t_tinterval As Long
t_day As Long
t_month As Long
t_year As Long
t_weekday As Long
End Type

Private Sub time_syn()
Dim t As TIME_OF_DAY
Dim tPtr As Long
Dim res As Long
Dim szServer As String
Dim days As Date
Dim todays As Date
'
' Replace \ntsig with your NT server that you want to get the time from
'
szServer = StrConv("\Trans_Hou", vbUnicode) 'Convert the server name to unicode
res = NetRemoteTOD(szServer, tPtr) 'You could also pass vbNullString for the server name
If res = 0 Then
CopyMemory t, ByVal tPtr, Len(t) 'Copy the pointer returned to a TIME_OF_DAY structure
days = DateSerial(70, 1, 1) + (t.t_elapsedt / 60 / 60 / 24) 'Convert the elapsed time since 1/1/70 to a date
days = days - (t.t_timezone / 60 / 24) 'Adjust for TimeZone differences

'Get local computer information for comparison

todays = DateSerial(70, 1, 1) + (DateDiff("s", DateSerial(70, 1, 1), Now()) / 60 / 60 / 24)
Me.Cls
Print DateDiff("s", DateSerial(70, 1, 1), Now()), todays, t.t_elapsedt, days

NetApiBufferFree (tPtr) 'Free the memory at the pointer

Else
MsgBox "Error occurred Call NetRemoteTOD: " & res, vbOKOnly, "NetRemoteTOD"
'Error 53: cannot find server
End If
End Sub

How To Get Time And Date From NT Server
Is it possible to retrieve time and date from a NT server to update and make shure i have the right time always. even if the user change it ???

Server´s Date-time
Hi everybody !!

How can I get the server´s date-time?
One way is with a store procedure, but maybe from vb...

Server Date And Time
how do i get server's date and time ?????

Server Date Time
Hi All,
It is required for my current project, that i want to fetch date and time from server. Im using VB6 and SQL 2000 server.
In some forms, i want to fetch the system date and time from the server which has different IP.
Can any one help me out with code?
Thanks in advance!!!
regards,
Koushik C.

Date / Time In Sql Server
How would I display only the time part of the datetime data type in SQL Server? I would like to display only the time in a text box.

Thanks in advance.

Shannan

Get Server DATE TIME.
hi,
I need to get the date and time of a private server in the local network..
how do i do this?
thanks,

Date And Time In SQL Server
Hi.

I have a table (SQLServer )Exam with 2 columns Date (DateTime) and Time (Datetime). In Date I store a date without time (format dd.mm.yyyy - 15.01.2004) and Time a time without a date (format HH:MM:SS - 12:33:00).

How to compare the current date and time (getdate()) whith theese two columns, so that only the date part of getdate() is compared with Date and only the time part of getdate() is compared with time.

Actually if I select rows from table the result for Date column is 2004-01-15 00:00:00.000
and for Date is 1899-12-30 12:33:00.000 (because of datetime type).

My select is something like this:

select .. from.. where
((getdate() 'date part'< exam.Date) OR
(getdate() 'date part' = exam.Date AND getdate() 'time part'<= exam.Time))
..

or maybe

getdate()<=exam.Date 'date part' + exam.Time 'time part'

Thanks for any suggestion.

Sebastian