SQL: can a date ONLY contain month/year info?

[darrel]

When using a SQL datetime field, is there a way to store ONLY the month and
year?

I have a situation where I need to store both month/year and month/day/year
data in the same column. Ie, some records will store the full date, some
will only be storing month/year.

Can the 'day' portion of date/time be null? Or can anyone think of a better
way to handle this?

Worst case, I may just tell the folks that they NEED to use a full
month/day/year and those that are just tracking month/year will have to deal
with a default day of '1'

-Darrel

[Jon Spivey]

Hi,
No a sql server datetime field must contain day month year and time - if
you miss out say the time sql server will add midnight. Does it have to a
datetime field? Why not make it a varchar and stick in month/year and
day/month/year as needed. If you needed to do something like sort by date
order you could use a case statement to stick a fictional day as needed and
then cast the field as datetime

You could insert say March 2005 (although not 3/2005) into a datetime field
but sql server will add 1 for the day so you'd end up with 1 March 2005 -
only trouble is you'd need another column to record whether the user
actually entered 1st March 2005 or just March 2005, this would be easy
enough but seems like making work for yourself

Jon

"darrel" <notreal@hotmail.com> wrote in message
news:d224vr$kif$1@forums.macromedia.com...

> When using a SQL datetime field, is there a way to store ONLY the month
> and
> year?
>
> I have a situation where I need to store both month/year and
> month/day/year
> data in the same column. Ie, some records will store the full date, some
> will only be storing month/year.
>
> Can the 'day' portion of date/time be null? Or can anyone think of a
> better
> way to handle this?
>
> Worst case, I may just tell the folks that they NEED to use a full
> month/day/year and those that are just tracking month/year will have to
> deal
> with a default day of '1'
>
> -Darrel
>
>

[Taffydog]

sql is limited in date handling only storing dates as yyyyy-mm-dd, however you
can format the date when displaying it in a table.
The syntax varies depending on the server technology used.
Using php you would display a date field in a table something like this
<?php echo makeDateTime($row_yourtable['date'], 'M j, Y'); ?>
this would display the date in the form of Jan 01, 2005
<?php echo makeDateTime($row_yourtable['date'], 'M, Y'); ?>
would display the date in the form of
Jan 2005
If you require the full month name change the M to F

[darrel]

> Hi,

> No a sql server datetime field must contain day month year and time - if
> you miss out say the time sql server will add midnight. Does it have to a
> datetime field? Why not make it a varchar and stick in month/year and
> day/month/year as needed.

Hmm...that is an *excellent* point. This field won't ever be used for
sorting or anything. Well...unless we use it for archiving. I'll have to
mull that over a bit. ;o)

> If you needed to do something like sort by date
> order you could use a case statement to stick a fictional day as needed

and

> then cast the field as datetime

Well, that's what I was thinking...since some dates are m/y, but some are
m/d/y, that I'd just always have a day in there...it's not really a big deal
if the day is 'fake'.

-darrel

[darrel]

> sql is limited in date handling only storing dates as yyyyy-mm-dd, however
you

> can format the date when displaying it in a table.

Thanks, Taffy!

-Darrel