Data design/ datetime
-
I am creating a database for a web application and a table will store training session information. Every session has a date and a time. Should I not just use one small-datetime field for this? I'm not sure how this can be updated on the front end.
-
Smalldatetime has a precision to the minute (can't store seconds).
Datetime has precision up to 1/333 th of second (3ms).
I think smalldatetime is the best choice in this case. The users will have to enter the sessiondate about like this :
2006/10/10 10:00
-
Would it be possible on the front end form for the date and tiem to appear as two field, even for updating?
-
You can always have 2 unbound text field. Then on the after_update event (I'm from an Access Background
), validate that you have a valid date and a valid time, then run the update statement on the server. -
Thanks.
-
In what language is the web app coded? In VB, VBScript, and other coding languages, it is easy to deparate a date and time into their parts by using a function that specifies one or the other.
In VB/VBScript, you would use the FormatDateTime() function. SQL Server doesn't have a function specifically for this purpose, but you can use the Convert() function with specifying a style.
Select
Convert(varchar, getdate(), 107) As theDate, Convert(varchar, getdate(), 108) As theTime
And there are a variety of styles from which to choose if you want the time or date in a different format.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
To be honest I'm not totally sure yet. I'm the SQL developer and we don't have anyone else. I got a book on ASP with C#. I have written some Java so C# looked the most familiar but I can learn anything. After finals next week, I'll have all the free time in the world to learn.
-
there was a post today referencing "Death By SQL", where the user slowly kills himself by separating date and time, when they can be stored in the same field....I think this is one of the worst practices in one of the articles here on SSC too.
make sure you store the date and time in the same field, and pull them out using the methods Robert mentions above. . Save yourself trouble later when you need to put them back together for comparisons and such.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yeah, that's what I'm out to accomplish.
-
Since you're using SQL 2005, if you really want to have separate date and time columns, I would use a datetime column to store the datatime and then add 2 persisted computed columns to the table that convert the datetime into integer version of the date and time. The cool thing about persisted computed columns is that they can be indexed as an actual value is inserted and maintained in the table.
So for example, let's say that I have a table called SalesReports with a column defined as: ReportDate datetime not null
I would then add the persisted computed columns.
Create
Table SalesReports (ReportID int not null identity(1, 1) primary key,
ReportDate
datetime not null default(getdate()))
-- Execute the following line 10 times to insert 10 values into table
Insert
Into SalesReports (ReportDate) Values(Default)
Select
* From SalesReports
-- Add the persisted columns
Alter
Table SalesReports Add iReportDate As Cast(Convert(varchar(35), ReportDate, 112) as int) PERSISTED,
iReportTime As Cast(Replace(Convert(varchar(35), ReportDate, 108), ':', '') as int) PERSISTED
Select
* From SalesReports
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply