April 25, 2002 at 10:07 pm
I need to store the date and the time component from a datetime variable in separate fields (meant for date and time respectively). How do I do it?
April 25, 2002 at 11:45 pm
There are quite a few possibilities, here's a sampling.
You could use string fields for both. The drawback to that is if you're trying to retrieve all the records between two dates, then string parsing is required.
You could break the date into tinyint fields for day, month, and year, and you could break time up similarly. There are some issues doing a date comparison. For instance, if you want to find everything between month = 4 day = 15 and month = 5 day = 10, it can be a little tricky, but still doable.
One of the things we're doing on a warehousing side is we're storing the date in a smalldatetime field (the time will default to 00:00:00 for that particular field but we're not concerned with that). We're storing hours and minutes in separate tinyint fields. In this manner, if we want to know everything that corresponds between 4/15/02 and 5/10/02, it's not too bad. Also, we still have access to the hour and minute as well. Searches are based upon just date (give me all the records occurring between 4/15/02 and 5/10/02), just hour (give me all the records occuring between 9 AM and 5 PM), or a combination of both (give me all records occurring between 4/15/02 and 5/10/02 during the workday).
These are just some examples. Which direction you should go, including with examples not given here, are based on two questions:
1) What goal are you accomplishing by splitting out date and time?
2) How are you planning on searching on the data?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 26, 2002 at 5:40 am
Definitely have to have a good reason for doing it in my opinion. Splitting out into separate/different data types means you give up all the date functions functionality. Hows that for a sentence? I'd rather see it split up in a view, or if performance became an issue, set up as computed columns that are indexed.
Andy
April 28, 2002 at 11:19 am
Thanks a lot folks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply