Issues converting strings to datetime

Question

I have a table in an ms-sql database which contains dates in a column like so:

2012-10-31 00:00:00.000

This column is listed as type 'datetime'. As in, in ms sql server management studio, I expand the table, then columns, and see the Dates(PK, datetime, not null).

Then in c#, I get those dates w/ a statement and assign it to a variable which populates a column of a grid on a web page made w/ asp.net. The sql statement is just getting the last entry of a user.

        SqlCommand command = new SqlCommand();
        //connection info here
        sql2 = "select max(day) as day from users u join days d on d.User_ID = u.id where u.ActiveUser = 1 and u.id = " + Users["ID"].ToString();;
        command.CommandText = sql2;
        dates["Entry"] = command.ExecuteScalar();

This populates the dates in the column like so:

10/31/2012 12:00:00 AM

Now, I have no idea why it's getting converted like that. I was following the data while be debugging and it changes w/ the command.ExecuteScalar() function. The format I would like to have is:

10/31/2012

without the hh/mm/ss AM.

I could not get ANY DateTime conversions/parses/formatting to work properly. I've spent four days on this. I asked a similar question here: Converting date to proper format , trying all the different suggestions.

What I'm doing in the meantime is:

string dt = command.ExecuteScalar().ToString();
dt = dt.Substring(0, dt.IndexOf(" ") + 1); 
dates["Entry"] = dt;

Which gets rid of all the text after the space in the date string that was returned. This would be okay, except that I wanted to compare these dates I'm getting, so I need them in DateTime. Trying to convert these strings to DateTime just would not work.

Edit: Definitions and types -

DataRow dates;
//for loop here going through for each user
dates = _dtuserhours.NewRow(); // _dtuserhours is a DataTable
//Sql statements here
_dtuserhours = new DataTable("newtable");
DataColumn column;
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Entry";
_dtUserHours.Columns.Add(column);

have you tried using date as the column data type instead of datetime? — Ryan Fitzpatrick
– Ryan Fitzpatrick, Commented Dec 8, 2015 at 15:50
Good suggestion but I can't change anything w/ the database. I'm allowed to get whatever from it to read, but nothing to modify it. — pfinferno
– pfinferno, Commented Dec 8, 2015 at 15:59

The Sharp Ninja · Accepted Answer · 2015-12-08 16:45:30Z

5

First, you need to change your SQL string to use command parameters, otherwise you are open to a SQL Injection attack.

Next, You can shape the output of your DateTime field in SQL Server to output the format you are looking for. What you are getting is the default format for DateTime depending on your culture.

Or, you could easily just use the string formatter property of the DateTime object that is returned like this:

dates["Entry"] = ((DateTime)command.ExecuteScalar()).ToString("MM/dd/yyyy");

EDIT: Try this instead:

dynamic result = command.ExecuteScalar();
dates["Entry"] = result.ToString("MM/dd/yyyy");

EDIT of the EDIT - set to dynamic because the compiler doesn't know what ExecuteScalar is returning at compile time.

edited Dec 8, 2015 at 16:45

answered Dec 8, 2015 at 15:55

The Sharp Ninja

1,0519 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

pfinferno Over a year ago

I will definitely put in parameters. Someone suggested the string formatter property on my other question before, but this is the error I get "InvalidCastException was unhandled by user code. Specified cast is not valid."

The Sharp Ninja Over a year ago

What is the data type of the day column in the database?

pfinferno Over a year ago

Right clicking on it and going to properties, it says Data Type is datetime. All lowercase.

The Sharp Ninja Over a year ago

if you put the result in a variable and check the type with reflection, what does it return? Are you showing us ALL of your code?

pfinferno Over a year ago

If I add a string variable = command.ExecuteScalar().ToString(), the value is equal to "10/31/2012 12:00:00 AM". However, the dates["Entry"] value is empty and the type is System.DBNull. I have showed all my code.

|

eftpotrm · Accepted Answer · 2015-12-08 16:33:28Z

2

If you're having problems getting .Net to convert the date to the exact format you want, you can always convert it to a formatted string in the SQL instead.

See http://www.techonthenet.com/sql_server/functions/convert.php for a list of the formats available and how to use them. In this case it'd be something like

SELECT CONVERT(nvarchar, max(day), 101) AS day 
FROM users u JOIN days d ON d.User_ID = u.id 
WHERE {etc, and parameterise your query!}

edited Dec 8, 2015 at 16:33

answered Dec 8, 2015 at 16:21

eftpotrm

2,2911 gold badge20 silver badges24 bronze badges

1 Comment

pfinferno Over a year ago

I've tried this using the '101' value for the mm/dd/yyyy but was unsure how to add this into my sql statement, or if I needed a new statement completely? (very new to sql)

HashPsi · Accepted Answer · 2015-12-08 16:51:41Z

1

Calling ToShortDateString on the result should give you the date in the desired format:

dates["Entry"] = ((DateTime)command.ExecuteScalar()).ToShortDateString()

For this to work as expected, you will need to change the data type for column dates["Entry"] to DateTime:

column.DataType = typeof(DateTime);

edited Dec 8, 2015 at 16:51

answered Dec 8, 2015 at 15:58

HashPsi

1,3912 gold badges12 silver badges22 bronze badges

4 Comments

pfinferno Over a year ago

Same problem as the answer below, giving an InvalidCastException.

HashPsi Over a year ago

@pfinferno Most likely, the issue is with the definition of dates. Please post how dates is defined

pfinferno Over a year ago

I think you and sharp ninja are on to it. dates is defined as DataRow dates;

pfinferno Over a year ago

Getting an InvalidCastException again when doing that :/

Abdul Rehman Sayed · Accepted Answer · 2015-12-15 13:38:04Z

So you have two issues :

1) Show the Date time column in a specific format : (i.e MM/dd/yyyy)

2) Use This Column For Datetime comparison also.

Here are two Approaches :

A) Have two columns (not a good way of solving things but gets work done) in your datatable.

i. One column for display (which will be of datatype string which you have made above).

ii. Another column with DateTime datatype for comparing.

B) Manipulate a single DateTime Column

i. From What I see, Entry Date has a datatype string, change it to datetime & fill it without any string conversion via the Executescalar.

ii. Wherever you are displaying EntryDate column, make an inplace conversion using toString("MM/dd/yyyy") method.

iii. Wherever you are Comparing, just cast it to Datetime & compare.

Collectives™ on Stack Overflow

Issues converting strings to datetime

4 Answers 4

13 Comments

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

13 Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related