asked on

date string..put it according to month, day and year

I have three field

Date Month day year
1-30-2008
12-11-2006
1-15-2007

my other fields have no records. Just the Date field has records. How would I divide the date so it updates the corresponding fields accordingly
something like this

Date Month day year
1-30-2008 1 30 2008
12-11-2006 12 11 2006
1-15-2007 1 15 2007

Thanks

NIMTUG_Simon

Select [Date], Month([Date]) as [Month], Day(]Date]) as [Day], Year([Date]) as [Year] from Table

You can add these as calculated fields in your table.

syedasimmeesaq

ASKER

but how do I update the table? the above query will give me just the view and would not update the records

Thanks

NIMTUG_Simon

Sry was thinking SQL Server

Might still work if not use

DatePart

Usage: DatePart(interval, date)

Interval may be set to:
yyyy for year
q for quarter
m for month
y for day of year
d for day
w for weekday
ww for week
h for hour
n for minute
s for second

Returns: Interval part of date.

syedasimmeesaq

ASKER

whats that? I have no idea how to use the above suggestion
Thanks

ASKER CERTIFIED SOLUTION

NIMTUG_Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

syedasimmeesaq

ASKER

shouldn't it be

update table set month = month(date), day = day(date), year = year(date);

????????????????

syedasimmeesaq

ASKER

ok no worries posted before looking at your second comment. Basically the same thing...I will try it now.
Thanks

NIMTUG_Simon

It doesn't matter about the order and the Table1 prefix is optional. You should alway use [ ] around column names that are the same as in-built Access functions.

syedasimmeesaq

ASKER

thanks