Time for another question - 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

Time for another question

Author

Topic

Ishamael
Starting Member

14 Posts

Posted - 2006-06-22 : 00:01:58

Ok I know this probley doesnt belong here but I am having trouble with a update query from Visual Basic to my sql server.

the string it is sending is
Update LastRun Set LastWeek = '22/06/2006 10:47:56 AM'

the date there is actuly a variable
"Update LastRun Set LastWeek = '" & lastDate & "'"

now if I type in manually(instead of variable) say 02/06/2006 that works

and the actul value of the cell in the table at the moment is
1/06/2006 9:49:54 AM

which to my eyes looks like the same format as above...

but when i try to run the query with the variable in place i get back a error saying that it cant convert char to datetime because the datetime is out of range.
table, column etc names are all fine, its just not accepting the variable instead of a manually typed date.

note if i take the ' ' from the vairable it returns a syntax error.

thank you and sorry in advance.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-22 : 00:04:44

You need to format the date first so SQL server understands it. Otherwise, you'll get whatever the default string representation of a date in VB is:

 "Update LastRun Set LastWeek = '" & Format(lastDate, "dd-mmm-yyyy hh:mm:ss") & "'"

Ishamael
Starting Member

14 Posts

Posted - 2006-06-22 : 00:17:57

Thankyou so much, it's been killing me for hours.. feel kinda stupid now:P

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-22 : 01:20:19

We have all been there, and if you really want to stay out of trouble then use the format "yyyymmdd hh:mm:ss" (no idea what that translates into in VB though), it should work no matter what language setting your server connection uses.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-22 : 02:29:56

dd-mmm-yyyy hh:mm:ss will work regardless because the month is output in alpha. But your way would probably be quicker.....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-22 : 04:06:54

>>use the format "yyyymmdd hh:mm:ss" (no idea what that translates into in VB though),

Yes it will work correctly so as yyyy-mm-dd hh:mm:ss

>>dd-mmm-yyyy hh:mm:ss will work regardless because the month is output in alpha.

Yes it also. But somewhere I read it wont work if the server is in French language(I'm not sure about it)

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-23 : 01:45:24

quote:
Yes it will work correctly so as yyyy-mm-dd hh:mm:ss

That has been claimed a few times and I think it is about time to put that to rest. Try out this:

SET LANGUAGE us_english

PRINT CAST('2006-05-31' AS datetime)

SET LANGUAGE french

PRINT CAST('2006-05-31' AS datetime)

it gets even worse if you start using textual month reprensentation. So once and for all, the universal string notation for dates are 'YYYYMMDD hh:mm:ss.mmm'.

EDIT: before someone blame it on the french, then it also fails for italian, portuguese, spanish, german, danish and norwegian (and then I stopped testing).
-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-23 : 01:56:41

Good Point PSamsig

dd-mmm-yyyy also fails in French Server



SET LANGUAGE us_english

PRINT CAST('31-May-2006' AS datetime)

SET LANGUAGE french

PRINT CAST('31-May-2006' AS datetime)

Result

Changed language setting to us_english.
May 31 2006 12:00AM
Changed language setting to Français.
Server: Msg 241, Level 16, State 1, Line 8
Syntax error converting datetime from character string.

Madhivanan

Failing to plan is Planning to fail

mymatrix
Starting Member

24 Posts

Posted - 2006-06-28 : 08:26:52

Very informative information given by all of you.

But the error which Ishamael was encountering is
'cant convert char to datetime because the datetime is out of range'
which was because he was trying to specify string value to datetime column.

I think a better solution would be if he first convert that string value to datetime value and then applying format(yyyyMMdd) to it will transfer a correct value to database.

any comments to this?

thnks
Gaurav

Even my blood group says be -ve to all the negatives.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 08:46:12

Thats what suggested previous replies

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-02 : 09:12:58

Specify a date as a string litteral IS the normal way to pass at date the server. The problem is that the server tries to convert that litteral according to the language setting of the connection, and if you are not carefull, you get in trouble. If you tried out my example you will get:
Msg 242, Level 16, State 3, Line 7
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. on the second one, or in other cases you will get the error Madhivanan posted, it all depends on where in the conversion process the server fails.
From what I could determin Ishamael´s lastDate WAS some kind of date/time type, but the default string conversion wasn´t appropriate for the language setting for his SQL server connection. So instead of figuring out which is configured wrong, it is much safer to to assume the worst and always format explicitly in a language neutral way.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-02 : 10:36:24

quote:
Originally posted by PSamsig

Specify a date as a string litteral IS the normal way to pass at date the server.
... snip ...
So instead of figuring out which is configured wrong, it is much safer to to assume the worst and always format explicitly in a language neutral way.

No. It is much safer to use parameters with proper datatypes.

if the variable D is a date in VB, C#, or whatever language you are using, the format is irrelevant if you simply use parameters declared as the proper type. Formatting should never come into play when interfacing data to or from SQL Server. If it does, you are making things harder and more complicated on yourself.

If the original code used a stored procedure with a datetime datatype, there'd be no problem. Even with in-line SQL, you can write:

(warning: psuedo code)

datetime d = <a valid date in your client language>
string s= "Update LastRun set LastWeek = @Value"
command c = new command(s)
c.parameters.add("@Value", sqltype.datetime).value = d
c.executeNonQuery()

That will always work, regardless of any date format settings at the client or at the server. And it is what you should *always* use, never concatenate together user input with sql strings. If you do, then not only is sql injection a possibility, but now you need to deal with formatting as well.

Keep it simple, do it right.

End of rant ... I just never understand why people insist on doing things the hard way !

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-02 : 11:11:05

Well true, I was thinking of covering parameters as well, but (wrongly) thought it would muddy up the discussion, because you in the end from time to time end up writing the these kind of queries hardcoded, like most of the examples and answers on this site. The advantages of parameters is a subject all on its own.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Ishamael
Starting Member

14 Posts

Posted - 2006-07-03 : 23:02:26

wow guys i didnt think to see this thread still being used:) thankyou all for your input, hope others benifit from it. really dont like datetime.
bosses got me on access again now(oh joy..), think he is to scared to let me near sql server again:P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources