Serious Datatype issue - 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

General SQL Server Forums

New to SQL Server Programming

Serious Datatype issue

Author

Topic

MelTed
Starting Member

9 Posts

Posted - 2008-07-22 : 08:50:42

Hello,
This is the first time i'm working with SQL Server and it's server 2000. I've chosen the datatime data type for a column to store dates from my app. My problem is that I'm getting the error "The conversion of a char datatype to a datetime datatype resulted in an out-of-range datetime value". My front end is VB6 and I'm inputing the system date directly into the database. My OS is XP and my regional settings are English United States but the short date format is customized to dd/mm/yyyy. I've tried saving the date and time, just the date alone, I've tried formating a mask edit control for the date format, but nothing seems to work.

Any assistence would be greatly appreciated.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-22 : 09:27:41

You should always use date variable in VB and express dates in YYYYMMDD HH:MM:SS format

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-22 : 09:59:40

The issue is with your front end. There should be no formatting to worry about at all. In VB6, you should be setting Date variables with the values that you want, and then passing those values to your SQL database using DateTime parameters. You never need to format anything if you simply use strong data typing and ensure to pass things using their correct datatypes.

Show us your VB code, that is where things need to be fixed.

- Jeff
http://weblogs.sqlteam.com/JeffS

MelTed
Starting Member

9 Posts

Posted - 2008-07-22 : 12:02:31

I've declared a variable as datatype date, however I still have the issue. The thing is I need to display the date as date only. jsmith8858 suggested that i post my, so here goes

This is in the Form Load:
medtDate.Text = Format(Now, (Date)) This displays the date only.

The save procedure:
CurDate = medtDate.Text

INSERT INTO Table (Date_Received) VALUES CurDate

I hope this is enough information for you guys.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-22 : 12:14:23

SQL Server 2000 will always display the date with the time part, so
12/31/2007 will show as 12/31/2007 00:00:00.000. Your front end app should format it to be just the date.

Jim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-22 : 14:41:41

You are just showing some pseudo-code, doesn't help us at all. What datatype is CurDate? How are you passing the value of that CurDate variable to your SQL statement?

Computer programming is kinda funny -- the specifics are actually pretty important.

- Jeff
http://weblogs.sqlteam.com/JeffS

MelTed
Starting Member

9 Posts

Posted - 2008-07-22 : 16:21:09

Hi again,

The following is the declaration of Curdate:
Private CurDate As Date

Private Sub Form_Load()

medtCurrentDate.Text = Format(Now, (Date))

End Sub

Save Procedure:
Private Sub cmdSave_Click()

Call Globalconnect 'Connection string

CurDate = medtCurrentDate.Text

SQLInsert = "INSERT INTO Transac_Table (TransNumber, Date_Received,Quantity, Amount)" & _
"VALUES ('" & TCode & "', '" & CurDate & "', '" & txtQuantity.Text & "', '" & txtAmount.Text & "')"

connect.Execute SQLInsert

MsgBox "Data saved", vbOKOnly

Call Clear_all 'clearing cells after save

connect.Close

End Sub

jsmith8858, i hope this is clear. I thought the info submitted before hit the basic points. My bad, lol.

Thanks for replying

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-22 : 16:29:03

Either you need to use parameterized queries or you need to convert your date STRING into the correct format as Madhivanan pointed out above (YYYYMMDD HH:MM:SS) before you make your SQL call.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-22 : 16:30:50

The issue is that you are not using parameters. Thus, your nice Date value must be converted to a STRING -- in a specific format -- when you are concatenating together your big SQL string to be executed. You are also opening yourself up to SQL Injection -- a very serious security issue. (see: http://en.wikipedia.org/wiki/SQL_injection )

Using parameters makes your code shorter, cleaner, easier, more efficient, safer and you are passing data by value and not just concatenating things into big string expressions.

see: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

for more details. Learn to use parameters and always use them when providing data for SQL statements and/or stored procedures from your client code.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-22 : 16:31:48

quote:
Originally posted by Lamprey

Either you need to use parameterized queries or you need to convert your date STRING into the correct format as Madhivanan pointed out above (YYYYMMDD HH:MM:SS) before you make your SQL call.

Let me fix that for you:

quote:
Originally posted by Lamprey

~~Either~~ you need to use parameterized queries. ~~or you need to convert your date STRING into the correct format as Madhivanan pointed out above (YYYYMMDD HH:MM:SS) before you make your SQL call.~~

There! Now it's perfect.

- Jeff
http://weblogs.sqlteam.com/JeffS

MelTed
Starting Member

9 Posts

Posted - 2008-07-24 : 14:43:10

Hi guys,
Thanks for your suggestions. Jeff, I've read the info on your links and I've tried to implement the parameterized quieries, but unfortunately I've haven't quite gotten the hang of implementing it in VB6. Still searching for direction on that.

Thanks though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources