insert date issue 1

[holidayIT]

I have two databases, i wrote a vbscript to take the data from one database, and insert it into another. the problem is that in one database, all of the data is in a single field. so i have too split the data into seperate variables, then write my insert statement. everything now works with the exception of my datetime field.

i insert the date in the format INSERT INTO <table> (<fields>) VALUES ('string', 6/24/04, <number>);

But when i query my table, the datetime field becomes 12:00am

It doesn't show the date!!! I am guessing it has somehting to do with the conversion from string, but i cannot figure it out. does this look familiar to Anyone?!?!?!

 

[SQLBill]

What does your query look like?

Also, datetime should be entered with single quotes:
Not:
VALUES ('string', 6/24/04, <number>);
But like this:
VALUES ('string', '6/24/04', <number>);

-SQLBill

Posting advice: FAQ481-4875

 

[RileyCat]

See if you can deduce the code for your application from this example . . .

declare	@TstDate	datetime

select	@TstDate = '06/30/2004'

create table #TmpTable
(
	Field1	char(15),
	Field2	datetime,
	Field3	int
)

insert	#TmpTable
values	('Hello World 1', '06/28/2004', 1)

insert	#TmpTable
values	('Hello World 2', getdate(), 2)

insert	#TmpTable
values	('Hello World 3', '06/28/2004 02:10', 3)

insert	#TmpTable
values	('Hello World 4', @TstDate, 4)

select	*
from	#TmpTable (nolock)

drop table #TmpTable

Good Luck! Repost here if you still need some assistance -- after you try writing the script yourself.






Stay Cool Ya'll!

-- Kristin

 

[holidayIT]

that was it, and i feel pretty stupid. i did it with quotes at first, but got a conversion error. I had had carriage returns at the end of the string that i had to parse, and never tried the sinlge quotes again as i thought they were wrong. thanks!

 

[donutman]

Maybe it's treating the 6/24/04 as two integer divisions. Try putting it in single quotes.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Sorry...had an old page in my browser.

 

[holidayIT]

thanks though donutman. don't apologize, i'm the idiot that forgot the single quotes. hehe.