Convert dd/MM/yyyy to sql server format

[Assimalyst]

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

The format of these values in the database is
yyyy-MM-dd hh:mm:ss.000

Thanks

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

What are you doing? are you using a SP or a query?

These are the steps:

1- Make sure the text is correct ! , why not use a Calendar instead of a
Textbox?

2- Convert the text to DateTime :
DateTime.ParseExact( textbox.Text, "dd/MM/yyyy" ,
DateTimeFormatInfo.InvariantInfo);
3- If you are using a sp, it's easy :
command.Parameters.Add("@assigneddate", SqlDbType.DateTime).Value =
DateTime.ParseExact( textbox.Text, "dd/MM/yyyy" ,
DateTimeFormatInfo.InvariantInfo);

4- If you are using a query then yo ushould use a parameterize query:
string selectSQL = "SELECT * FROM table1 WHERE tehdate= ? ";
SqlCommand selectCMD = new SqlCommand(selectSQL, theconn);
selectCMD.Parameters.Add("@thedate", SqlDbType.NVarChar, 15).Value =
DateTime.ParseExact( textbox.Text, "dd/MM/yyyy" ,
DateTimeFormatInfo.InvariantInfo);

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Assimalyst" <c_******@hotmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

The format of these values in the database is
yyyy-MM-dd hh:mm:ss.000

Thanks

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

What are you doing? are you using a SP or a query?

These are the steps:

1- Make sure the text is correct ! , why not use a Calendar instead of a
Textbox?

2- Convert the text to DateTime :
DateTime.ParseExact( textbox.Text, "dd/MM/yyyy" ,
DateTimeFormatInfo.InvariantInfo);
3- If you are using a sp, it's easy :
command.Parameters.Add("@assigneddate", SqlDbType.DateTime).Value =
DateTime.ParseExact( textbox.Text, "dd/MM/yyyy" ,
DateTimeFormatInfo.InvariantInfo);

4- If you are using a query then yo ushould use a parameterize query:
string selectSQL = "SELECT * FROM table1 WHERE tehdate= ? ";
SqlCommand selectCMD = new SqlCommand(selectSQL, theconn);
selectCMD.Parameters.Add("@thedate", SqlDbType.NVarChar, 15).Value =
DateTime.ParseExact( textbox.Text, "dd/MM/yyyy" ,
DateTimeFormatInfo.InvariantInfo);

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Assimalyst" <c_******@hotmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

The format of these values in the database is
yyyy-MM-dd hh:mm:ss.000

Thanks

[Lasse Vågsæther Karlsen]

Assimalyst wrote:

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

The format of these values in the database is
yyyy-MM-dd hh:mm:ss.000

Thanks

To "properly" query the database, use a Command object and store the
date in the parameters, using the DateTime type, and letting the
database objects handle the transition from .NET to the database.

If you need to rewrite it so that it can be used in a string to query
the database, use DateTime.ToString("format here") and specify the
correct format, "yyyy-MM-dd HH:mm:ss.000" would probably do the trick.

--
Lasse Vågsæther Karlsen
http://www.vkarlsen.no/
mailto:la***@vkarlsen.no
PGP KeyID: 0x2A42A1C2

[Lasse Vågsæther Karlsen]

Assimalyst wrote:

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

The format of these values in the database is
yyyy-MM-dd hh:mm:ss.000

Thanks

To "properly" query the database, use a Command object and store the
date in the parameters, using the DateTime type, and letting the
database objects handle the transition from .NET to the database.

If you need to rewrite it so that it can be used in a string to query
the database, use DateTime.ToString("format here") and specify the
correct format, "yyyy-MM-dd HH:mm:ss.000" would probably do the trick.

--
Lasse Vågsæther Karlsen
http://www.vkarlsen.no/
mailto:la***@vkarlsen.no
PGP KeyID: 0x2A42A1C2

[Adam Clauss]

"Assimalyst" <c_******@hotmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

The format of these values in the database is
yyyy-MM-dd hh:mm:ss.000

Thanks

Use DateTime.ParseExact and pass it the exact format you are giving it.

--
Adam Clauss

[Adam Clauss]

"Assimalyst" <c_******@hotmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

The format of these values in the database is
yyyy-MM-dd hh:mm:ss.000

Thanks

Use DateTime.ParseExact and pass it the exact format you are giving it.

--
Adam Clauss

[Stephen Russell]

"Assimalyst" <c_******@hotmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

string SQL ="Select * from yourTable were yourDateColumn =
'"+this.procedureDateTxtBx.ToString()

that is open for SQL injection but it will work. You could also have a SP
in the db where your :
exec yourProc procedureDateTxtBx.ToString()

[Stephen Russell]

"Assimalyst" <c_******@hotmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Hi i have a value entered into an asp text box,
procedureDateTxtBx.Text, thet has the format dd/MM/yyyy. I need to
convert this into a format recognisable by SQL server in order to
properly query the database.

I've tried a few DateTime.Parse methods but can't get any to work.

string SQL ="Select * from yourTable were yourDateColumn =
'"+this.procedureDateTxtBx.ToString()

that is open for SQL injection but it will work. You could also have a SP
in the db where your :
exec yourProc procedureDateTxtBx.ToString()