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
LVL 11
sciber_dudeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
datetime of course.
0
ShogunWadeCommented:
or smalldatetime which has a granularity on 1 minute.   but also has a smaller date range.
0
sciber_dudeAuthor Commented:
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,DateColumn,101) + ' 12:00 PM'

:) 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sciber_dudeAuthor Commented:
or should i store the variable as a varchar "yyyy-mm-dd hh:mm:ss"

:) SD
0
ShogunWadeCommented:
ok

"Update MyDateTimeTable Set DateTimeColumn = convert(datetime,DateColumn,101) + ' 12:00 PM'"
this is better as it is unambiguous

UPDATE MyDateTimeTable Set DateTimeColumn=convert(datetime,DateColumn,112)  + ' 12:00'


"convert(datetime,DateColumn,101) " this is fine
0
ShogunWadeCommented:
oop cut & paste error .....

"Select * from MyDateTimeTable Order by DateTimeColumn ASC"  this is correct
0
ShogunWadeCommented:
Storing it as varchar will cause you a whole host of problems later on.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sciber_dudeAuthor Commented:
> 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
0
ShogunWadeCommented:
Your welcome.
0
sciber_dudeAuthor Commented:
sorry to slip in another question into this...

Select Max(DateTimeColumn) from MyDateTimeTable

Will this work?
:) SD
0
ShogunWadeCommented:
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(DateTimeColumn)) from MyDateTimeTable   produces the same as the above, but using a function.  

As you can see the datetime being stored by SQL server internally in this way gives a lot of flexibility
0
sciber_dudeAuthor Commented:
Thank you very much!

:) SD
0
ShogunWadeCommented:
pleasure
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.