Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-03-24 : 07:52:06
|
| John Thomas writes "I am new to database programming and am developing a simple application in VB.NET. My question might seem obvious but I was hoping for an easy to understand but thorough explanation of what is the best way to store a date field (not really worried about time except in 1 table). My database currently has 8 tables. 4 of the tables have a "date" field (no time required). 1 table has 2 fields which are "time" fields (start time and end time).Initially, I was going to store each of these fields as VarChar (8 for the Dates (01/02/05) and 6 for the Times (15:47)). But then I read up on SmallDateTime and thought that might be the better way to go. The problem is I don't know which is actually better in terms of performance of the database.Is there a significant performance hit when storing dates and times as VarChar versus SmallDateTime? Also, is the a significant "size" difference (in the database itself) for SmallDateTime versus VarChar(8) or VarChar(6)?Thank you in advance for any help that you can give me." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-24 : 07:55:21
|
| Smalldatetime is 4 bytes, regular datetime is 8 bytes, so they will save you space. You can also do date arithmetic on smalldatetime/datetime using DateAdd, DateDiff, and extract parts of a datetime using DatePart, DateName, etc. You cannot do this as easily with varchar, unless you convert it to datetime...so it makes no sense to store it as varchar.If you need to format the date a specific way, look at the CONVERT function in Books Online. It has several format codes used for dates that can turn it into a varchar of any format. You would only use this if you NEEDED a specific format, and cannot format it in your reporting package or application. |
 |
|
|
|
|
|
SmallDateTime versus VarChar? Performance Hits?