Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2012-02-06 : 20:42:08
|
| I am creating a table that will house the information on upcoming game releases (Country, Title, Publisher, and Date) and I have setup everything but the date column. I originaly was going to use it as a DATE type but then I could not figure how I would do things like "Early/Mid/Late MONTH" or "First/Second/Third/Fourth quarter, YEAR" or even a simple "No Date set". So then I was going to use varchar(20) but that would make it hard for a begginer like me to sort based on release date or remove entires that are XX days past. So can anyone make any suggestions on the best data type to use and how to address said issuse of that data type?-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
Jaffary
Starting Member
1 Post |
Posted - 2012-03-22 : 22:57:54
|
| im also a biggennerIf you want to use varchar, use date format as yyyy-mm-ddit will help you to make queries.I think DATE type supports all your requirements, first you have to define quarters date rangei will try then let u know. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-22 : 23:09:51
|
Don't use varchar for date. use DATE data type or DATETIME if you need to store the time as well.for Early/Mid/Late month, depending on your definition, eg before 10th of the month,where datepart(day, DateCol) <= 10 for further information and how to get the quarter, check out datepart() in BOL.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-03-23 : 04:02:33
|
| You should NOT use varchar for dates, DATE or DATETIME are the only options you should consider. Actually there is nothing to consider, there is absolutely no good reason to store dates as varchar. The DATE and DATETIME datatypes comes with quite a few built-in functions that help you sort and filter on just about anything you want. Think about this: how would you select all records in your database for the last 30 days if you had your dates as varchar? With the datetime datatype you could do like this SELECT * FROM table where DateColumn > GETDATE()-30- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
gtabetnj
Starting Member
8 Posts |
Posted - 2012-03-30 : 11:47:49
|
| You have two business needs here, and therefore one field won't doUse a DATE field for the basic storage, as a product is released on a specific dateBut you need a set of reference (at least a date dimension and probably the few reference table to support and build that dimension from) that allow for assigning dates to specific years, weeks, months quarters, fiscal quarters, etc. these are then used for your reporting about activity related to a quarter, etc., by linking the date in the software release table to the same date in the Date reference data to define the quarter, year, fiscal period, etc.Let me know if you need help with thisgej |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2012-04-05 : 07:09:45
|
| Thanks for the input all but I think everyone is miss understanding. When I reference things like "Early/Mid/Late MONTH" or "First/Second/Third/Fourth quarter, YEAR" or "No Date set". I am not talking that a specific date has been given but the company releasing the game might have simply just stated "First Quarter of 2013" or have not even announced a date yet so I am still stuck on how to represent this non-numerical figures along side normal dates.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
|
|
|
|
|
Recomendations for column type.