DateTime Asp.NET

Question

I'm trying to save a date in database with ASP.NET. The datatype of Date in the database is datetime. I want to save it as for example (23/02/2014 00:00:00)

That is, I want to save Date, Month and Year, and then have time always be 00:00:00. I also want to have zeros before month and/or day when they are only one number. e.g. 02/06/2014 instead of 2/6/2014.

This is possible when the datatype is varchar but how would I do this when the datatype is datetime?

I can't understand your question. What is it you can do with varchar but not with date, save time as zeros? — Patrick, Feb 23 '14 at 14:43
When saving dates in a database you're not saving a format string, you're saving the date. It's up to the consumer of the data to format it when reading it... — Patrick, Feb 23 '14 at 14:44
Why does it matter the format in the DB? DateTime is a date time and how it looks in the DB doesn't change that... you can format however you want later. One way you can run into problems though is if you are trying to insert MM/DD/YYYY when it expects DD/MM/YYYY, etc. — MikeSmithDev, Feb 23 '14 at 14:45
@MikeSmithDev: And when using C# you might as well send the SqlCommand the DateTime instance directly (if using parameters), and that won't be a problem. — Patrick, Feb 23 '14 at 14:46
My leader want the datatype to be datetime. I want to store in database the date like this (dd/MM/yyyy 00:00:00), which means it will store the date and time will always be 00:00:00 — aldoblack, Feb 23 '14 at 14:53

roy.d · Accepted Answer · 2014-02-24T11:41:57.417

1

dateTime.ToString("dd/MM/yyyy");

will set the time to zero. MM and dd will add zero if needed

SQL will know the correct date if you add this:

DateTime.Now.ToString("dd/MM/yyyy",System.Globalization.CultureInfo.GetCultureInfo("he-IL"));

update

System.Globalization.CultureInfo info = CultureInfo.CurrentCulture;
          Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("he-IL");

            command.Parameters.AddWithValue("@info",DateTime.Now);
          Thread.CurrentThread.CurrentCulture = info;

edited Feb 24 '14 at 11:41

answered Feb 23 '14 at 15:30

roy.d

1,030
2
16
33

It does not have DateTime.ToString, only DateTime.Now.ToString, but ehwn I do 'DateTime.Now.ToString("dd/MM/yyyy 00:00:00");' it give me an error like this 'Arithmetic overflow error converting expression to data type datetime' because the datatype in database is datetype not varchar. I need it to be datatype – aldoblack Feb 23 '14 at 15:35
you need to set right before you add the date to the parameters the System.Globalization.CultureInfo.GetCultureInfo("he-IL")) to the current theard and when you done set current back to the original culture – roy.d Feb 23 '14 at 15:38
I tried it and still shows 'Arithmetic overflow error converting expression to data type datetime'. :/ – aldoblack Feb 23 '14 at 15:44
add using for it using System.Threading; using System.Globalization; let me know if you done with it – roy.d Feb 23 '14 at 15:53
What should I write instead of "someDate" ? Because it shows me "The name "someDate" does not exist in the current context. – aldoblack Feb 23 '14 at 15:58
Instead of 'Convert.ToDateTime(someDate.ToString("dd/MM/yyyy"))' I wrote 'Convert.ToDateTime(DateTime.Now.ToString("dd/MM/yyyy"))' and still the date in database is '2/23/2014 12:00:00 AM' I want it to be '23/02/2014 00:00:00' – aldoblack Feb 23 '14 at 16:01

score 0 · Answer 2 · answered Feb 23 '14 at 15:07

0

You should store the DataTime on the database without problem using Parameter for sample:

command.Parameters.Add("DateField", SqlDbType.DateTime, datetime);

When you want to show on asp.net page, you should get the DateTime struct and format it , for sample:

label1.Text = dateTime.ToString("MM/dd/yyyy");

To know more about dateTime formats, look this link on MSDN documentation: http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

answered Feb 23 '14 at 15:07

Felipe Oriani

37,948
19
131
194

I know that but. I can store it if datetype is varchar like this `command.Parameters.AddWithValue("@info", DateTime.Now.ToString("dd/MM/yyyy - 00:00:00"));` The thing is that it is giving me error when I try to save it as datype is datatime. – aldoblack Feb 23 '14 at 15:15
Well, there is no way to save a DateTime in a VarChar column because the Parameter will generate the SQL for DateTime if you specify DataType.DateTime in a varchar column. For varchar, you have to specify the DataType as varchar (or string) and sabe a string there. – Felipe Oriani Feb 23 '14 at 19:58

score 0 · Answer 3 · answered Feb 23 '14 at 15:24

0

Just use a date type parameter and the Date property.

command.Parameters.AddWithValue("@info", DateTime.Now.Date);

answered Feb 23 '14 at 15:24

Ceres

3,524
3
18
25

The time is 12:00:00 AM. I want it to be 00:00:00 and date is 2/23/2014, I want it to be 23/02/2014 Date first and then month an it must be with 0 like 02 not 2. – aldoblack Feb 23 '14 at 15:34
@Ceres: Why not use `DateTime.Today` direct instead? – Patrick Feb 23 '14 at 22:58
@aldoblack, What you are talking about is date formatting, 12:00:00 AM and 00:00:00 are the same time. There is no way to control how the date is stored internally using the datetime datatype. Unless you change the datatype to a varchar, what you are talking about doesn't make sense. – Ceres Feb 24 '14 at 13:22

DateTime Asp.NET

3 Answers3

Linked