I need yyyy-mm-dd hh:mm:ss in SQL Server to become yyyy-mm-dd in C#

Question

HI,

This might be a frequent question.

I have a date column (InvDate) in SQL Server 2005 database, which has a value like 2009-04-23 00:00:00.000. I am fetching this into a Dataset. Now i need to store only 2009-04-23 into some output Excel sheet (i am using OleDB). I dont want the time , even if it is just 00:00:00.000.

I have tried CONVERT(varchar(11),InvDate,101) and Parse.ToDateExact, and Convert.ToDate .

I am not able to get the right steps .

Any ideas ?

Thanks.

Answer 1

DateTime.Date is your answer, this retrieves the date part of the object, i.e. time = 00:00:00.000

        DateTime date = DateTime.Now;
        Console.WriteLine("Date is " + date.Date);

Answer 2

If you have DateTime member instance then you can call method "ToShortDate()" function.

Answer 3

If your dataset has a DateTime it will also hold 2009-04-23 00:00:00.000. You can than format this object in your output code:

date.ToString("yyyy-MM-dd");

Answer 4

If you have the data as a string:

DateTime.Parse(yourDataAsAString).ToString("yyyy-MM-dd")

Answer 5

If you are writing your date to Excel:

Excel.Worksheet worksheet;
...
worksheet.Cells(row, column).Value = dateVar.Date; // returns the date part of dateVar
worksheet.Cells(row, column).NumberFormat = @"yyyy-mm-dd";

Answer 6

In your SQL query, you can just specify that you don't want the time part of the time/date value by using this syntax:

 SELECT DATE(timedatecolumn) WHERE whatever = whatever

If you want to get really fancy, you can use DATE_FORMAT and have it output it just about any way you like. For instance:

SELECT DATE_FORMAT(timedatecolumn, '%W, %M %D, %Y') WHERE blah = blah

would output (if the date were today):

 Thursday, April 23rd, 2009

The various options for date formats (in MySQL) are at:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

Also, bear in mind that the date value in Excel (if it is formatting it as an actual date, not just as a string) is not ACTUALLY what you see in the cell. It is the Microsoft date value (basically some integer that correlates to a date and time within Microsoft logic), so if you want Excel to recognize and treat the value as a date (say, if you want to run a function or formula based on the values that are older or on a specific month, etc) you just need to format the column to not show the time part of the value.

If you want to drop the time because you don't want events that are marked as happening an hour later to be treated differently, you just need to round the value down using the FLOOR function:

FLOOR(somedatecell, 1)

Since the time part of the value is always a decimal following the the date part of the value, this will round it down from, say 39926.2725925926 (today at 6:32:32 AM) to just 39926 (today at midnight).

You still have to format the cell to get rid of the time part, but now all dates which are equal are actually equal, and not off by any hours or minutes.

However, if you simply want the date as a string, you will have to convert the datevalue to text using the TEXT function:

 TEXT(somedatecell,"mm/dd/yyyy")

If you want to be super-anal(like me, but this isn't necessary) you could round the date value down to midnight first and then convert it to text using this formula:

 TEXT(FLOOR(somedatecell, 1), "mm/dd/yyyy")

Excel basically does that for you when you say you want to format the text using the date format, but just in case you like things extra neat, you can round down first.

Answer 7

Use String.Format to format the date as you want, or DateTime.ToString.

To handle this in the SQL is just more cumbersome than necessary. Convert in your C# code before handling the string representation of the DateTime value over to Excel.

Or, if you're adventorous, you could give Excel the DateTime value, and then add a format on the Excel spreadsheet cell to show the value correctly. This would be the most correct approach, as the value would still be a date, also for Excel.