how to convert db2 date string to sql string.( SQL automatically converting to datetime)

Question

0.00/5 (No votes)

See more:

Hi Friends,

I need to fetech some datafrom DB2 and paste in SQL. I am able to do that but some problem in date format.

Date format in DB2 is like 2003-09-23 12:32:09.095499

but when i insert into sql its became 9/23/2003 12:32:09 PM

actually my DB2 field is a string not a date. but sql converts it into date

i am feteching data from DB2 with help of dataset and datatable. In the dataset itself dateconversion occur.

How can i solve this

My SQL quey for feteching data from DB2

SQL

SELECT KEY_TMS, CAT_TRDCTL, DT2_BILL, CAT_BILL, DT2_FROM, KEY_ACCT, DT2_ME, AMT_MKTVAL FROM TBL_BILL

this is how i am passing value to sql

C#

cmd.Parameters.Add("@KEY_TMS", SqlDbType.VarChar).Value =dt.Rows[i]["KEY_TMS"].ToString();

Edited***
As per suggestion i made date formating
but millisecond portion is not working as expected

C#

keytms_date = Convert.ToDateTime(dt.Rows[i]["KEY_TMS"].ToString());
cmd.Parameters.Add("@KEY_TMS", SqlDbType.VarChar).Value = keytms_date.ToString("yyyy-MM-dd hh:mm:ss.ffffff");

its showing 000000 insted of 095499
how can i solve this issue

Posted 19-Mar-15 1:31am

jinesh sam

Updated 19-Mar-15 2:02am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Joan Magnet · Answer 1 · 2015-03-19T01:39:00

Solution 1

To fix it change .ToString() and format the string.

Take a look at ustom Date and Time Format Strings

I suppose you have declared this field as varchar instead of datetime in SQL database.

Posted 19-Mar-15 1:39am

Joan Magnet

Comments

jinesh sam 19-Mar-15 8:04am

Thanks for your support
I made changes as per your suggestion but millisecond portion is not working
i have updated the question please look into it

Joan Magnet 19-Mar-15 8:11am

If KEY_TMS is a string you shouldn't convert it to datetime.

Try to assign it to a string variable without conversion.

jinesh sam 19-Mar-15 8:18am

let me check

jinesh sam 19-Mar-15 8:23am

string date = dt.Rows[i]["KEY_TMS"].ToString();

but its displays with am pm

i cannot use like this its showing an error
string date = dt.Rows[i]["KEY_TMS"].ToString("yyyy-MM-dd hh:mm:ss.ffffff");

Joan Magnet 19-Mar-15 8:26am

Can check dt.Columns["KEY_TMS"].DataType ?

Joan Magnet 19-Mar-15 8:30am

No I meant:

string date = dt.Rows[i]["KEY_TMS"].ToString();

Joan Magnet 19-Mar-15 8:30am

Lunch time, BBR

jinesh sam 19-Mar-15 8:45am

its DateTime.

Joan Magnet 19-Mar-15 10:02am

You said it is a string.

Do you need to save it as string or as DateTime?