Back devices: How can I format a datetime field in a stored procedure

How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?

CONVERT(varchar, youddatecolumn, 101).

Check out the CAST and CONVERT functions in BOL for more formats.

|||

Use T-SQL Convert function and instead of returning date object return string representation of that date.

Or, return date object from Stored Procedure and convert it in C# code to appropriate format.

|||ived been properly arranging the quotes and single quotes but still it has error...it changes the error but has error still...

'2006' 付近に不適切な構文があります。 (There is an improper syntax in the vicinity ..'20 06'... )

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: '2006' 付近に不適切な構文があります。

Source Error:

Line 113: sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)Line 114:Line 115: sqlcom.ExecuteNonQuery()Line 116: MyConn.Close()Line 117: sqlcom.Dispose()

MyConn.Open()
sqlcom.CommandType = CommandType.Text
sqlcom.CommandText = "update TE_zangyou Set ninka_nen_from = @.ninka_nen_from ,ninka_gatsu_from = @.ninka_gatsu_from, ninka_hi_from = @.ninka_hi_from ,ninka_nen_to = @.ninka_nen_to, ninka_gatsu_to = @.ninka_gatsu_to, ninka_hi_to = @.ninka_hi_to, ninka_ji_from = @.ninka_ji_from,ninka_ji_to = @.ninka_ji_to,ninka_bun_from = @.ninka_bun_from,ninka_bun_to = @.ninka_bun_to,ninka_day_name_from = @.ninka_day_name_from,ninka_day_name_to= @.ninka_day_name_to,ninka_date_kyou= @.ninka_date_kyou where syain_No = " + Request.QueryString("syain_No")+ " and date_kyou ='" + Request.QueryString("date_kyou") + "' and time_kyou ='" + Request.QueryString("time_kyou") + "'"

but the format in my database is like this:"2006/12/12" and its just character string...

so it will just compare whether they have thesame date_kyou...

but it produces errors like that!

can u help me determine my mistake? thank you..:-)

|||

Two things here:

1.Please use all parameters for your WHERE clause variables;

2.What data types are these columns in your database table?

Datetime column may be hard to use at the beginning, but it is worth it. Try it.

|||

Hi,

is your datafield varchar or datetime?

if it's a varchar field containing the date in a string format 'yyyy/mm/dd', it may be a syntax error.
You can try debugging your appln and providing us with the value of the data in your code to check if theres a syntax error with "textbox_approval_date.Text".

if it's a datetime field, you can try converting the value in "textbox_approval_date.Text" to a datetime object first before passing it into your statement using the CONVERT function.

Hope this helps.

|||

its only character string... and its working when i just put requerystring(syain_No) but when i included the date_kyou and time_kyou,.,,, it produces that kind of error...

what do u think is the problem here?

|||

Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

sqlcom.Parameters.AddWithValue("@.ninka_nen_from", TextBox1.Text)
sqlcom.Parameters.AddWithValue("@.ninka_gatsu_from", TextBox2.Text)
sqlcom.Parameters.AddWithValue("@.ninka_hi_from", TextBox3.Text)
sqlcom.Parameters.AddWithValue("@.ninka_day_name_from", TextBox5.Text)
sqlcom.Parameters.AddWithValue("@.ninka_ji_from", textbox_from_hr.Text)
sqlcom.Parameters.AddWithValue("@.ninka_bun_from", textbox_from_min.Text)
sqlcom.Parameters.AddWithValue("@.ninka_nen_to", TextBox6.Text)
sqlcom.Parameters.AddWithValue("@.ninka_gatsu_to", TextBox7.Text)
sqlcom.Parameters.AddWithValue("@.ninka_hi_to", TextBox8.Text)
sqlcom.Parameters.AddWithValue("@.ninka_day_name_to", TextBox9.Text)
sqlcom.Parameters.AddWithValue("@.ninka_ji_to", textbox_to_hr.Text)
sqlcom.Parameters.AddWithValue("@.ninka_bun_to", textbox_to_min.Text)
Label28.Visible = True
textbox_approval_date.Visible = True
textbox_approval_date.Text = Date.Today
statusbox_approve_mesg.Visible = True
sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)

sqlcom.ExecuteNonQuery()
MyConn.Close()
sqlcom.Dispose()
MyConn.Dispose()
MsgBox(" Application Dates was Approved")

TextBox1.Enabled = False
TextBox2.Enabled = False
TextBox3.Enabled = False
TextBox6.Enabled = False
TextBox5.Enabled = False
TextBox7.Enabled = False
TextBox8.Enabled = False
TextBox9.Enabled = False
textbox_from_hr.Enabled = False
textbox_from_min.Enabled = False
textbox_to_hr.Enabled = False
textbox_to_min.Enabled = False
Reason.Enabled = False
compensantory.Enabled = False
Edit1.Visible = False
Button_save.Visible = False
Button_approve.Visible = False
Button_reject.Visible = False
Button_send.Visible = True

&&&& my code... what u think? there is an error in thebold words

|||Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

sqlcom.ExecuteNonQuery()
MyConn.Close()
sqlcom.Dispose()
MyConn.Dispose()
MsgBox(" Application Dates was Approved")

&&&& my code... what u think? there is an error in thebold words

and date_kyou and time_kyou are character string only...

|||

Change to this and try:

...

where syain_No =@.syain_No AND date_kyou=@.date_kyou AND time_kyou=@.time_kyou

.....

sqlcom.Parameters.AddWithValue("@.syain_No", Request.QueryString("syain_No"))

sqlcom.Parameters.AddWithValue("@.date_kyou", Request.QueryString("date_kyou"))

sqlcom.Parameters.AddWithValue("@.time_kyou,Request.QueryString("time_kyou"))

|||

パラメータ化クエリ '(@.syain_No nvarchar(4),@.date_kyou nvarchar(12),@.time_kyou nvarch' にはパラメータ @.time_kyou が必要ですが、指定されていません。 (It is not specified though parameter @.time_kyou is necessary for (@.syain_No nvarchar(4), @.date_kyou nvarchar(12), and @.time_kyou nvarch Ceri of making to the parameter ''. )

Exception Details:System.Data.SqlClient.SqlException: パラメータ化クエリ '(@.syain_No nvarchar(4),@.date_kyou nvarchar(12),@.time_kyou nvarch' にはパラメータ @.time_kyou が必要ですが、指定されていません。

Source Error:

Line 117: sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)Line 118:Line 119: sqlcom.ExecuteNonQuery()Line 120: MyConn.Close()Line 121: sqlcom.Dispose()

Source File:C:\Documents and Settings\mspitc5\My Documents\Visual Studio 2005\MSPITC_project\overtime_application_approval_inputt.aspx.vb Line:119

Stack Trace:

&&&

it produces different error...

|||

Hi,

can you check if all the parameters are created and input with valid values?
if it doesn't solve the problem, would it be possible to translate the japanese words in your error statements as not many of us here understands it...
but i think you can narrow down the problem to the parameter @.time_kyou

|||Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Dim sqlcom As SqlClient.SqlCommand = MyConn.CreateCommand()

sqlcom.ExecuteNonQuery()
MyConn.Close()
sqlcom.Dispose()
MyConn.Dispose()
MsgBox(" Application Dates was Approved")

&&&& my code... what u think? there is an error in thebold words

and date_kyou and time_kyou are character string only...

its only character string... and its working when i just put requerystring(syain_No) but when i included the date_kyou and time_kyou,.,,, it produces that kind of error...

what do u think is the problem here?

&&&

error

ived been properly arranging the quotes and single quotes but still it has error...it changes the error but has error still...

wat u think about it?

'2006' 付近に不適切な構文があります。 (There is an improper syntax in the vicinity ..'20 06'... )

Exception Details:System.Data.SqlClient.SqlException: '2006' 付近に不適切な構文があります。

Source Error:

Line 113: sqlcom.Parameters.AddWithValue("@.ninka_date_kyou", textbox_approval_date.Text)Line 114:Line 115: sqlcom.ExecuteNonQuery()Line 116: MyConn.Close()Line 117: sqlcom.Dispose()

|||

Hi,

im assuming that your data type fordate_kyou andtime_kyou are nvarchar(12)

what is the value of the query string?
kindly check if theres any ' in your querystring which will spoil your sql statement.
to minimise this error, use <string>.Replace("'", "''")
Example:

Cstr(Request.QueryString("time_kyou")).Replace("'", "''")

im afraid if this doesnt work you would have to provide the actual value of the query string...

Sunday, February 19, 2012

How can I format a datetime field in a stored procedure

'2006' 付近に不適切な構文があります。 (There is an improper syntax in the vicinity ..'20 06'... )

'2006' 付近に不適切な構文があります。 (There is an improper syntax in the vicinity ..'20 06'... )

No comments:

Post a Comment

Back devices

Blog Archive

About Me