CODEWITHSUNDEEP
c#asp.netsqlsql-server
user2321895
user2321895

Reputation: 87

Conversion failed when converting date and/or time from character string in gridview

In my GridView I can see my date column as mm/dd/yy normally but when I select a record and assign a string like so

GridViewRow row = GridView1.SelectedRow;
string sdate= row.Cells[2].Text; //Date is in Column 2

When I output this sdate string it returns: 1368309600000

And If I try to convert it, it doesn't work and I'm trying to select something from a SQL database and I get this error

Conversion failed when converting date and/or time from character string

EDIT: The part in the SQL statement for conversion is:

convert(datetime, '" + sdate +"', 101"+")

I suspect the problem is in the sdate itself but I'm not sure

Upvotes: 0

Views: 3490

Answers (2)

Ben Babu
Ben Babu

Reputation: 1

UpdateCommand="UPDATE [dbo].[ENTRY] set [_DATE] = CONVERT( date  , @_DATE ,103)  where   [NO] = @NO  "

important part here is 103 it is for format dd-mm-yyyy check out your local format. this problem only rises for people outside America.

Upvotes: -2

davmos
davmos

Reputation: 9577

Your date string appears to be expressed as the number of milliseconds since January 1, 1970, 00:00:00 GMT. The T-SQL CONVERT() function doesn't support this format. So, you could do a conversion to a C# DateTime like this...

DateTime date = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
                   .AddMilliseconds(Convert.ToDouble(sdate));

1368309600000 converts to 11/05/2013 22:00:00Z (this is UTC)

Then adjust for local time and reformat the date for your SQL string like this...

"convert(datetime, '" + date.ToLocalTime().ToString("MM/dd/yyyy") + "', 101)"

Upvotes: 0

Related Questions