You would likely want to use datetime or smalldatetime.
Using a varchar would have other impacts - things such as
sorting. Ordering on a string date column orders the values
by the string, not what you would expect for date ordering.
How the values are displayed would be a different thing. You
can use convert to display the date only or you can use the
datename function to return string values - along the lines
of:
SELECT CONVERT(char(10), GetDate(), 101)
SELECT DATENAME(mm, GetDate()) + ' ' + DATENAME(dd,
GetDate()) + ' ' + DATENAME(yy, GetDate())
You can find more information on convert as well as date
functions in books online.
-Sue
On Tue, 17 Apr 2007 12:09:22 -0700, "thersitz"
[quoted text, click to view] <thersitz@gmail.com> wrote:
>if you are tracking historical dates in an sql db -- is the strategy to
>apply a character datatype to the field? -- since the datetime field ranges
>January 1, 1753, through December 31, 9999
>
>And what if you don't need the time as part of a date field?
>
If you need pre-1753 dates, datetime will not work for you.
If you only want dates from year 1 through 9999 you can format a character
string as yyyy-mm-dd and at least your sorting will make sense. If you want
dates prior to 1 you will need to include an era indicator of some sort
(maybe just a minus sign) such as (-/+)yyyy-mm-dd.
Of course, you will have to do your own date math and date formatting if you
are not using SQL date functions.
Here is one man's approach to using the CLR to extend datetime:
http://weblogs.sqlteam.com/mladenp/archive/2006/12/16/52754.aspx RLF
[quoted text, click to view] "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:f7sa23dqk349s15c5lilpiq8m3a61b2fb2@4ax.com...
> You would likely want to use datetime or smalldatetime.
> Using a varchar would have other impacts - things such as
> sorting. Ordering on a string date column orders the values
> by the string, not what you would expect for date ordering.
> How the values are displayed would be a different thing. You
> can use convert to display the date only or you can use the
> datename function to return string values - along the lines
> of:
>
> SELECT CONVERT(char(10), GetDate(), 101)
>
> SELECT DATENAME(mm, GetDate()) + ' ' + DATENAME(dd,
> GetDate()) + ' ' + DATENAME(yy, GetDate())
>
> You can find more information on convert as well as date
> functions in books online.
>
> -Sue
>
> On Tue, 17 Apr 2007 12:09:22 -0700, "thersitz"
> <thersitz@gmail.com> wrote:
>
>>if you are tracking historical dates in an sql db -- is the strategy to
>>apply a character datatype to the field? -- since the datetime field
>>ranges
>>January 1, 1753, through December 31, 9999
>>
>>And what if you don't need the time as part of a date field?
>>
>
