Best way to store date and time
Requirements:-
1) Need to store Date and time in either one variable or two.
2) Need to sort the Date and time in ASC and DESC order
3) Need to update the time in just one SQL statement.
4) Only hours and minutes are important while storing the Time
What would be the best way (datatype & format) to store the this kind of data? And what would be the SQL statements to do each of the above requirements?
:) SD
1) Need to store Date and time in either one variable or two.
2) Need to sort the Date and time in ASC and DESC order
3) Need to update the time in just one SQL statement.
4) Only hours and minutes are important while storing the Time
What would be the best way (datatype & format) to store the this kind of data? And what would be the SQL statements to do each of the above requirements?
:) SD
datetime of course.
or smalldatetime which has a granularity on 1 minute. but also has a smaller date range.
ASKER
well, let me explain my dilemma a little more:
If I store the date and time in one datetime variable... for e.g. I have the following SQL statements
/* Creating a Test Table */
Create Table MyDateTimeTable
(
DateTimeColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTimeTable values ('11/29/2004 10:00 AM')
insert into MyDateTimeTable values ('11/29/2004 01:00 PM')
insert into MyDateTimeTable values ('11/28/2004 12:00 PM')
insert into MyDateTimeTable values ('11/28/2004 01:00 PM')
go
######Will these SQL statements work?######
> Need to sort the Date and time in ASC and DESC order
Select * from MyDateTimeTable Order by DateTimeColumn ASC
>Need to update the time in just one SQL statement.
Update MyDateTimeTable Set DateTimeColumn = convert(datetime,DateColum
:) SD
PS: the problem is that I dont have too much experience using Microsoft SQL server and I dont have access to a SQL server database to test it out. However, I am familiar with writing Jet SQL for access.
If I store the date and time in one datetime variable... for e.g. I have the following SQL statements
/* Creating a Test Table */
Create Table MyDateTimeTable
(
DateTimeColumn datetime
)
go
/* Inserting the test value into the table */
insert into MyDateTimeTable values ('11/29/2004 10:00 AM')
insert into MyDateTimeTable values ('11/29/2004 01:00 PM')
insert into MyDateTimeTable values ('11/28/2004 12:00 PM')
insert into MyDateTimeTable values ('11/28/2004 01:00 PM')
go
######Will these SQL statements work?######
> Need to sort the Date and time in ASC and DESC order
Select * from MyDateTimeTable Order by DateTimeColumn ASC
>Need to update the time in just one SQL statement.
Update MyDateTimeTable Set DateTimeColumn = convert(datetime,DateColum
:) SD
PS: the problem is that I dont have too much experience using Microsoft SQL server and I dont have access to a SQL server database to test it out. However, I am familiar with writing Jet SQL for access.
ASKER
or should i store the variable as a varchar "yyyy-mm-dd hh:mm:ss"
:) SD
:) SD
ok
"Update MyDateTimeTable Set DateTimeColumn = convert(datetime,DateColum
this is better as it is unambiguous
UPDATE MyDateTimeTable Set DateTimeColumn=convert(dat
"convert(datetime,DateColu
"Update MyDateTimeTable Set DateTimeColumn = convert(datetime,DateColum
this is better as it is unambiguous
UPDATE MyDateTimeTable Set DateTimeColumn=convert(dat
"convert(datetime,DateColu
oop cut & paste error .....
"Select * from MyDateTimeTable Order by DateTimeColumn ASC" this is correct
"Select * from MyDateTimeTable Order by DateTimeColumn ASC" this is correct
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
> Storing it as varchar will cause you a whole host of problems later on.
This is exactly what i wanted to hear!!
Thank you,
:) SD
This is exactly what i wanted to hear!!
Thank you,
:) SD
Your welcome.
ASKER
sorry to slip in another question into this...
Select Max(DateTimeColumn) from MyDateTimeTable
Will this work?
:) SD
Select Max(DateTimeColumn) from MyDateTimeTable
Will this work?
:) SD
Yes that will also work. The thing to note is that a datetime field is implemented in sql using two integers (although sql doesnt let you see this detail). But the point here is that as it is numerical you can do alsorts of mathematical stuff on it.
If you perceive it like a floating point number the "whole " part is the day and the decimals are the time. thus
eg:
Select MAX(DateTimeColumn)-1 from MyDateTimeTable will give you the day before the max date
Ther are also a series of functions to do similar things: eg:
Select DATEADD(DAY,-1,MAX(DateTim
As you can see the datetime being stored by SQL server internally in this way gives a lot of flexibility
If you perceive it like a floating point number the "whole " part is the day and the decimals are the time. thus
eg:
Select MAX(DateTimeColumn)-1 from MyDateTimeTable will give you the day before the max date
Ther are also a series of functions to do similar things: eg:
Select DATEADD(DAY,-1,MAX(DateTim
As you can see the datetime being stored by SQL server internally in this way gives a lot of flexibility
ASKER
Thank you very much!
:) SD
:) SD
pleasure