CODEWITHSUNDEEP
c#sql-server-2005datetime
Chakra
Chakra

Reputation: 2557

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

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.

Upvotes: 2

Views: 6565

Answers (7)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391396

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.

Upvotes: 0

Anthony
Anthony

Reputation: 37065

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.

Upvotes: 0

Patrick McDonald
Patrick McDonald

Reputation: 65421

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";

Upvotes: 4

Sergio
Sergio

Reputation: 8259

If you have the data as a string:

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

Upvotes: 3

MarkO
MarkO

Reputation: 2233

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");

Upvotes: 0

Akash Kava
Akash Kava

Reputation: 39916

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

Upvotes: 2

Lazarus
Lazarus

Reputation: 43084

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);

Upvotes: 2

Related Questions