How to get rid of 12:00:00 AM in smalldatetime data type?

[adonet]

I insert date data only to SQL smalldatetime field and got :
5/29/2004 12:00:00 AM. How to get rid of 12:00:00 AM in smalldatetime data type?

 

[RiverGuy]

Use the Format() function.

 

[adonet]

Format() is working from "5/29/2004 12:00:00 AM " to "5/29/2004". But when insert into "5/29/2004" only, "5/29/2004 12:00:00 AM " will store in SQL server.

 

[RiverGuy]

Look up "datetime and smalldatetime" in the T-SQL section of Books Online. It doesn't work that way. Both data types store the date and the time.

I'm not sure why it matters that it has 12:00 AM. If you don't want it, use a datatype in which you can store character data.

 

[chiph]

But when insert into "5/29/2004" only, "5/29/2004 12:00:00 AM " will store in SQL server.

If the column type in SQL server is a DateTime type, that's what will be stored.

You can use a SQL CONVERT function to only return you a date value (drop the time part), but more than likely I would just ignore it, and filter it out when you display it, not in your data-access layer.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[PankajBanga]

Sometimes having time part in "Date of Birth" field doesn’t make sense.

Converting your date to short date string using ToShortDateString function before adding to data table should fix your probem.

http://home.iprimus.com.au/pankajbanga

 

[chiph]

Something I should have included in my earlier post is that this question falls into the common date/time area of how the storage of a date differs from the [/i]display[/i] of a date.

When you store a date (only) into a DB column of type DateTime, the database will automatically add a time value. The value it uses is 12AM (midnight). When you retrieve the date from the database, it's coming from a DateTime column, so of course it will have a time value on there. It'll be your job to format it for display.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first