DateTime Format

[Tamer_Ahmed]

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

[JABE]

Instead of concatenating the datetime var to the sql string, try setting it as a parameter to the command object.

[Tamer_Ahmed]

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

[Tamer_Ahmed]

i made it i changed the string to

"update UserInfo set DateLastOn= getdate()" Now It's Work

Thanks Every one

[Nerseus]

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:

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]

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]

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]

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]

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]

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'

[Joe Mamma]

this:

Build Dynamic Sql!!!!

should have read

DON'T Build Dynamic Sql!!!!

[Joe Mamma]

By the way. . .

 

the lead article in this months MSDN magazine addresses this issue in depth:

Stop SQL Injection Attacks Before They Stop You

[laredo512]

By the way. . .

 

the lead article in this months MSDN magazine addresses this issue in depth:

Stop SQL Injection Attacks Before They Stop You

 

 

Thanks, thats a much better answer. Besides, I am not building a web app...

 

Thanks again.

[Joe Mamma]

Thanks, thats a much better answer. Besides, I am not building a web app...

 

Thanks again.

 

doesnt have to be a web app

[laredo512]

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

 

[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

Edited August 29, 2004 by laredo512

[Joe Mamma]

try

 

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

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

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

[Joe Mamma]

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

 

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.

[laredo512]

I had to change your suggestion to

 

strQ.Parameters.add(new SqlParameter("strArrDate", strArrdate)

etc ...

 

now I get :

 

The conversion of char type data to a datetime data type resulted in an out of range datetime value.

 

?!?

[laredo512]

just a note/question. . .

 

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

 

 

User input .... a date time picker....

 

My system locale is English(Canada)... By default, the format of the date is dd/MM/yyyy h:mm:ss tt

[Joe Mamma]

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.

[laredo512]

So, what you are saying basically, for any of this date mayhem to end, in any app development, one should stick to OleDB ?

[Joe Mamma]

User input .... a date time picker....

 

My system locale is English(Canada)... By default, the format of the date is dd/MM/yyyy h:mm:ss tt

then, given arrDate is a date time picker (I use the checked parameter to determine if the user wants the date to be set. . .)

 

dim dtArrDate as object = null;
if arrDate.Checked then dtArrDate = arrDate.Value

' evaluate other GUI input and grab the values 
' and them pass them to a method that has calls like this: 

strQ.Parameters.add(new SqlParameter("arrDate", dtArrDate))

 

do you have the northwind DB on your system?

[laredo512]

then, given arrDate is a date time picker (I use the checked parameter to determine if the user wants the date to be set. . .)

 

dim dtArrDate as object = null;
if arrDate.Checked then dtArrDate = arrDate.Value

' evaluate other GUI input and grab the values 
' and them pass them to a method that has calls like this: 

strQ.Parameters.add(new SqlParameter("arrDate", dtArrDate))

 

do you have the northwind DB on your system?

 

I do have northwind.

 

By the way, your solution of convert.todatetime(strArrDate) solved my problem.

 

Thanks

[Joe Mamma]

oops I did it again!!!!

dim dtArrDate as object = null;

should have read . . .

dim dtArrDate as object = System.DBNull.Value

[laredo512]

ya, i read another post dealing with you explaining nulls, already intergrated in the code before this started.

 

Thanks