SQL Date format

[Simply Me]

I have DTPicker name as DTEntryDate and on form load I set it like this

  DTEntryDate.Value = Format$(Now, "mm/dd/yyyy") 'Display the Current Date

I used like this in my SQL INSERT

Format$(DTEntryDate.Value, "mm/dd/yyyy")

Why is it that when I look at the value saved in my table its like this 9/26/2007 12:00:00 AM? Its always 12:00:00 AM.
Questions:
1. Why is it that it is recorded as date and time wherein I specify "mm/dd/yyy" only?
2. How should I correct this to this play the current date and time? Or
3. How can omit the time part?

[Simply Me]

when I tried SELECT GetDate() it returns the correct date and time

[si_the_geek]

Thread moved to Database Development forum

Your code in Form_Load is very wrong... do not ever put a String (which is the data type that Format returns) into a Date (which is the data type of the DTP's .Value property). For more information, see the article Why are my dates not working properly from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

If you want to get the Date without the Time, simply use Date instead of Now, eg:

  DTEntryDate.Value = Date 'Display the Current Date

1. Why is it that it is recorded as date and time wherein I specify "mm/dd/yyy" only?

That is because of the data type of the field. In some database systems (but not all) there are several Date/Time data types, and most will store the default time (12:00 am) if you do not specify otherwise.

If you want to change the data type so it does not store the time, see the help for your database system to see how to do that (if it is possible).

2. How should I correct this to this play the current date and time? Or
3. How can omit the time part?

It depends how you are using that field.

[Simply Me]

I am using smalldatetime field. Im using SQL SERVER 2005 EXPRESS

[anhn]

Your table display the date field as 9/26/2007 12:00:00 AM
because that Date/Time field uses the default format "mm/dd/yyyy hh:nn:ss AM/PM"
12:00:00 AM is midnight, with 24hr time format it is 00:00:00
That is the decimal part of the equivalent Double value of a Date/Time value.

If you want that date field shows only the date, set its format to "mm/dd/yyyy"

[Simply Me]

Your table display the date field as 9/26/2007 12:00:00 AM
because that Date/Time field uses the default format "mm/dd/yyyy hh:nn:ss AM/PM"
12:00:00 AM is midnight, with 24hr time format it is 00:00:00
That is the decimal part of the equivalent Double value of a Date/Time value.

If you want that date field shows only the date, set its format to "mm/dd/yyyy"

If you refer to post #1 I have that already.

[anhn]

No, you don't understand what I said.

What you formated is for the value you add to the table.
What I said is the format of the field in the table.

When you have formated the field in the table as what you want when you create the table, every time when you add a value you don't need to format the value again.

[Simply Me]

What properties will I used? Im using sql 2005 express

[techgnome]

Does it matter? SQL is going to store the data how it needs to... and that includes the time... until SQL 2008 comes out next year, that's how it's going to be.... all you can do is format it when you extract it out of the database and displaying it in VB. Don't worry about what you see in the database.

-tg

[Simply Me]

Actually tg i only need the date not the time. I just would like to know how to remove it Im just learning ms sql, I used ACCESS before and im looking for the feature in SQL.

[szlamany]

Ok - we all have said this so many, many times.

You cannot get rid of the "time" that is stored in the database in MS SQL SERVER. A DATETIME field in MS SQL will store a date and time - that's it's job - that is what it does.

No way around this.

Stop trying to think of one - it does not exist.

Making sure the time is 12:00 AM or 00:00 is the best you can do.

And when you get the datetime field out of the database simply format it to show the date only.

We ALWAYS format in the query in our shop.

Convert(varchar(10),SomeDateField,101)

Style 101 is MM/DD/YYYY

We always pass dates from SQL to VB using the CONVERT() function so dates arrive in VB as the "date part" only.

@anhn - what did you mean by "When you have formated the field in the table "??