?
Solved

Best way to store date and time

Posted on 2004-11-29
13
Medium Priority
?
490 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:sciber_dude
  • 8
  • 5
13 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12698638
datetime of course.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12698641
or smalldatetime which has a granularity on 1 minute.   but also has a smaller date range.
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 12698773
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 11

Author Comment

by:sciber_dude
ID: 12698780
or should i store the variable as a varchar "yyyy-mm-dd hh:mm:ss"

:) SD
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12698818
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12698822
oop cut & paste error .....

"Select * from MyDateTimeTable Order by DateTimeColumn ASC"  this is correct
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 1000 total points
ID: 12698832
Storing it as varchar will cause you a whole host of problems later on.
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 12698860
> 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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12698866
Your welcome.
0
 
LVL 11

Author Comment

by:sciber_dude
ID: 12698904
sorry to slip in another question into this...

Select Max(DateTimeColumn) from MyDateTimeTable

Will this work?
:) SD
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12704098
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
 
LVL 11

Author Comment

by:sciber_dude
ID: 12705591
Thank you very much!

:) SD
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12705642
pleasure
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question