Insert sCRIPT AND OTHER - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Insert sCRIPT AND OTHER

Author

Topic

ramgopal
Starting Member

4 Posts

Posted - 2003-06-02 : 13:07:58

hai,

i have problem . There a about 35 to 4o tables with some 40 to 50 records each. This is data which is fixed and will not change. u can call these as specifications to be used for verifications in my application.

for this i generated insert scripts using VB 6 and ADO. as backing the database is giving big file

the problem: There is a table with datetime values and i treated them as strings and generated script.

the generated script is

insert into AC_MSTR(AC_YR,FROM_DT,TO_DT,USER_ID,RECORD_DT) Values (20032004,'01-04-03 6:32:49 PM','31-03-04','GOPAL','22-04-03 6:32:49 PM')

when i run the script in Query Analyzer i get an error saying

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Table structure:
CREATE TABLE AC_MSTR (
AC_YR numeric(8, 0) NOT NULL ,
FROM_DT datetime NOT NULL ,
TO_DT datetime NOT NULL ,
USER_ID varchar (6) NOT NULL ,
RECORD_DT datetime NOT NULL
) ON PRIMARY

AC_YR is primary key

1. How to rectify the error and make my scripts work.
2. How to store images in SQL Server (need to store images from my application)
3. How do u insert,update, query on binary data.

thank u and waiting for reply
Gopal

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-02 : 13:18:56

this is probably because of a profile using a different date format.
If you code your dates as yyyymmdd this will be independent of any settings and always work (nb yyyy-mm-dd will not).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-02 : 13:20:24

You need to convert your strings to datetime using a style.

insert into AC_MSTR(AC_YR,FROM_DT,TO_DT,USER_ID,RECORD_DT)
Values (20032004,CONVERT(datetime, '01-04-03 6:32:49 PM', 5),CONVERT(datetime, '31-03-04', 5),'GOPAL',CONVERT(datetime, '22-04-03 6:32:49 PM', 5))

See SQL Server Books Online for syntax of CONVERT. It will also show you the different styles that are available. I chose 5 for yours.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-02 : 13:21:24

I agree with nr. You should just code it so that your data can be accepted into datetime column instead of using CONVERT statement.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-02 : 13:21:24

quote:

1. How to rectify the error and make my scripts work.

Your date formats are wrong:

Try:

insert into AC_MSTR(AC_YR,FROM_DT,TO_DT,USER_ID,RECORD_DT)
Values (20032004,'01-04-03 6:32:49 PM','03-31-03','GOPAL','04-22-03 6:32:49 PM')

quote:

2. How to store images in SQL Server (need to store images from my application)

Don't. Store Pointers as URL's

quote:

3. How do u insert,update, query on binary data.

What are you storing as binary?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources