Inserting DATE into SQL-Server.

[Bert van den Dongen]

Hi,

How do you insert a record into a table
with a DateTime field in it, using VB6 ?

I thought:
Insert into, etc. Format(Text01,"DD-MM-YYYY")
But writing "14-11-2000" results in 14 may 1905
And "2000-11-14" results in somewhere in 1894!!!

I have 4 thick books on VB and SQL-Server
and I searched the books-on-line of SQL-Server
but was unable to find out what format to use.

I hope one you pro's can help.
Bert.

[Allan Mitchell]

I presume then that '14-11-2000' is in a text field on a VB form.

In SS this would map to a char or varchar datatype

declare @date varchar(20)
select @date = '11-04-14' --this is what you have now
select convert(datetime,@date) --this is how to convert to a proper
date and time.

Note SS cannot store one without the other hence the name DATETIME.
The only way to split is to use a char or varchar datatype

Allan

In article <8urvgl$76u$1...@nereid.worldonline.nl>,

--

Allan Mitchell
MCSE

Sent via Deja.com http://www.deja.com/
Before you buy.

[Bert van den Dongen]

Sorry, Allan, I don't understand what you write here.
Yes, the date is in a TextBox, so in a string-variable.
And yes, the field in the table is DateTime (8 bytes, I believe).

The READING of the date (Select * From ...) works perfect.
It is the WRITING (Insert and Update) that I can't figure out.

I have now:
Set Rs = DE02.Conn01.Execute("Insert Into Boekingen Values (" &
Format(Text01,"DD-MM-YYYY") & ")")

What should it be? Should I change the field in the table to varchar(20)?
Then why is there a fieldtype DateTime?
Should I add the time?
Please reply again.
Bert.

Allan Mitchell <a_mit...@bigfoot.com> schreef in berichtnieuws
8us7ua$kvr$1...@nnrp1.deja.com...

[webm...@sqlwarehouse.com]

Hi

If your running Visual Basic and ADO , you can do a insert off of the ADO
connection ,
assuming your ado connection is named adocon then your insert could be as
simple as ..

text1.text = "07/04/1976"
adocon.execute "insert into your-table-name (your-date-datefield) values ('
" & text1.text & " ')"

just encapsulate the vbdates in ticks . ' and you should be ok

Take care ,

- Joe -
Joe Povilaitis
Webmaster
www.SQLwarehouse.com

"Bert van den Dongen" <Ber...@WorldOnline.nl> wrote in message
news:8urvgl$76u$1...@nereid.worldonline.nl...

[Bert van den Dongen]

Hi Joe,

I also tried your version, but it is not accepted since it now has the
apostrophes and the fieldtype = DateTime.

I tried all possible formats, like:
Format(Text1.Text,"DD-MM-YYYY") (returns a datetime somewhere in 1894)
Format(Text1.Text,"YYYY-MM-DD") (returns a datetime somewhere in 1905)
Format(Text1.Text,"YYYY-MM-DD" + " 00:00:00") (not accepted: syntax error
near 00)
Format(Text1.Text,"YYYY-MM-DD") + " 00:00:00" (also syntax error near 00)
CDate(Text1.Text) (returns another weird date, but also cannot be right
since in immediate pane it shows the year in 2 digits)

It seems such a simple problem but I'm searching for almost a week now:
How to insert a text from Text1.Text into a (SQL-Server 7) DateTime field?
.....Execute("Insert Into Table1 Values (" & ????? & ")"

Thanks for replying, Joe, hope you have another solution.
Bert.

Webm...@SQLwarehouse.com <webm...@SQLwarehouse.com> schreef in
berichtnieuws iEGQ5.8404$M51.1...@typhoon.ne.mediaone.net...

[Bert van den Dongen]

Hi, Allen, Joe and others.

A friend of mine found the solution:
You must use the format MM-dd-yyyy,
first the month!
And indeed, Joe, WITH apostrophes.

Bert.