DateTime Format

Tamer_Ahmed

Centurion
Joined
Dec 20, 2003
Location
Egypt
hi
i'm using this Query
dim objlaston as date = datetime.now
dim st as string =
"update UserInfo set DateLastOn= CONVERT(DATETIME,'" + objlaston + "', 102)"
the result will be like this
update UserInfo set DateLastOn= CONVERT(DATETIME,'29/05/2004', 102)
SQL Server throw exception
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.
well if i changed the date format to 2004 - 05 - 29
the query will work correctly the question here is how the variable objlaston return the date in this format year - m - day or how to make sql server accept my format
 

Tamer_Ahmed

Centurion
Joined
Dec 20, 2003
Location
Egypt
Thanks Jabe for reply but i used parameter and it's not working
i used parameter called @objLaston and i gived it value = date.now
and i get the same error
 

Nerseus

Danner
Joined
Oct 22, 2002
Location
Arizona, USA
The "standard" install of SQL Server (with the standard formats of English) expects dates in the Month/Day/Year format. You can format your DateTime variable that way, if you like. A SqlParameter would also work though it appears you had problems and went with another solution :)

If you want to format a .NET DateTime variable, try something like:
Visual Basic:
dim st as string = "update UserInfo set DateLastOn= '" + objlaston.ToString("MM/dd/yyyy") + "'"

You'll note I removed the convert date/time as that would happen automatically in the sample above.

-nerseus
 

Tamer_Ahmed

Centurion
Joined
Dec 20, 2003
Location
Egypt
Thanks Nerseus it's work my problem was how to change the format and u told me the solution Thanks alot and the dataadapter didn't work coz the parameter's value will have the wrong format dd/mm/year
anyway Thanks Again
 

laredo512

Regular
Joined
Jan 6, 2004
Location
Far enough to see snow in winter
A continuation if you please...

I have an English(canada) locale on my machine. Running MSDE2000A (of course, the locale is English(US) on this)

How do I go about entering the right dates in the addTableRow events of the datasets so that I can use the simple dataAdapter.Update command?

Or am I to type the whole insert statement manually as above?

Thanks
 

Joe Mamma

Senior Contributor
Joined
Mar 1, 2004
Location
Washington DC
Build Dynamic Sql!!!!

Use Parameters!!!!

No Need To Worry About Date-time Format Of The Underlying Rmdbs.
And You Shouldnt Have To Worry About It!!!!

Dates Are Dates!!!
Strings Are Strings
Use Dates To Update Dates!

Please!!!
Dynamic Sql Is Bad!!!
Very Bad!!!
 

laredo512

Regular
Joined
Jan 6, 2004
Location
Far enough to see snow in winter
Joe Mamma said:
Build Dynamic Sql!!!!

Use Parameters!!!!

No Need To Worry About Date-time Format Of The Underlying Rmdbs.
And You Shouldnt Have To Worry About It!!!!

Dates Are Dates!!!
Strings Are Strings
Use Dates To Update Dates!

Please!!!
Dynamic Sql Is Bad!!!
Very Bad!!!


Since you seem keen on giving it to us "inexperienced" users on how dynamic sql is bad, why dont you post something constructive instead so that we can learn from and improve... at least to keep us at bay with "bad" questions.

Lets be assertive.

Thanks
 

Joe Mamma

Senior Contributor
Joined
Mar 1, 2004
Location
Washington DC
laredo512 said:
Since you seem keen on giving it to us "inexperienced" users on how dynamic sql is bad, why dont you post something constructive instead so that we can learn from and improve... at least to keep us at bay with "bad" questions.

Lets be assertive.

Thanks
do an advanced search of this forum for User Name: 'Joe Mamma' and key word: 'parameters'

search this site for User Name:'Joe Mamma' and key word: 'SQL Security'
 

laredo512

Regular
Joined
Jan 6, 2004
Location
Far enough to see snow in winter
Joe,

I must admit, I am at an impass at the moment. I have read the examples you pointed out, but still seem to fail in creating a parameterized query. I use vb.net to code my application.

I fail to comprehend the @ symbol in the query. I know they refer to the parameters, but I cant see the relation between those @field in a parameterized query and those inside the IDE generated update statements for each sqlDataAdapter I created.

In other instances, the ? is used inside the query string and then the command.parameters(integer).value = me.datePicker.value

I have attempted the latter (with the ? )

Forgot to mention: strArrdate and strStorDate are Dim as Date

Visual Basic:
[code]
strQ.Connection = Me.cn

                            strQ.Connection.Open()
                            strQ.CommandText = "insert into tblcontainer(fldarrivaldate, fldstoragedate, fldtotalweight) VALUES(?, ?, ?)
                            strQ.Parameters(0).Value = strArrDate
                            strQ.Parameters(1).Value = strStorDate
                            strQ.Parameters(2).Value = decWeight
                            strQ.ExecuteNonQuery()
                            strQ.Connection.Close()
                            Exit Select
[/code]

and I get

Invalid index 0 for the SQLParameterCollection with count 0

What am I doing wrong?

Thanks
 
Last edited:

Joe Mamma

Senior Contributor
Joined
Mar 1, 2004
Location
Washington DC
try

strQ.Parameters.add(new OleDbParameter("strArrDate", strArrDate))
strQ.Parameters.add(new OleDbParameter("strStorDate",strStorDate))
strQ.Parameters.add(new OleDbParameter("decWeight",decWeight))
 

Joe Mamma

Senior Contributor
Joined
Mar 1, 2004
Location
Washington DC
Joe Mamma said:
try

strQ.Parameters.add(new OleDbParameter("strArrDate", strArrDate))
strQ.Parameters.add(new OleDbParameter("strStorDate",strStorDate))
strQ.Parameters.add(new OleDbParameter("decWeight",decWeight))
just a note/question. . .

where is strArrDate coming from??? a user input? a dataset????

you might have to do

Visual Basic:
strQ.Parameters.add(new OleDbParameter("strArrDate", Convert.ToDateTime(strArrDate)))

Let your System Locale settings control date display and format.
As long as the string going into the DateTime Parameter matches the Locale settings the translation should be fine. But in good design, this shouldn't matter.

Data entering the data layer should already be formatted. Formatting takes place at the Presentation layer. If the data comes from a DateTime control, it shouldn't be a string anyway and no problem.

I use the terms 'Data Layer' and 'Presentation layer' in abstract, as you might only have a 1-tier application. But even in your 1-tier, DB routines should be removed from your GUI.

If the data is coming from a persisted data store, the data set should be a datetime already. . . Unfortunately, many people want to format in the data layer (which is not correct) and they think putting dates out in the format of YYYYMMDD and returning them as integers is helpful. UGH!!!!

BTW, DateTimes are actually floating point numbers at a low level with some extra methods.
 

Joe Mamma

Senior Contributor
Joined
Mar 1, 2004
Location
Washington DC
Oh yeah. . .

I use OleDB classes in my examples as my work must be db agnostic.

there are some ideosyncratic differences between the SqlClient/OracleClient/ODBC/OleDB namespaces.

If you use OleDB, your code 'should' work against any database. Also, I dont believe there is any guarantee that SqlClient and OracleClient namespaces will remain consistent from version to version. ODBC should never change as it is a dead library.
 
Top Bottom