Parse a date using Year, Month functtions? 1

[craigorama]

I have a field [new_time] that displays full date and time like this:
15/05/2006 12:20:00 PM

I'm trying to parse it up and insert it into new columns.
I searched the forums here and found this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1257953

I've tried to incorporate the year function into a statement but it hasnt worked correctly. Here is my statement:

First I've added the field:
ALTER TABLE TELLUS_TEMP_UPLOAD ADD LOC_YEAR Date;

Then i've tried to update the new field with the year value of the [new_time] field:
UPDATE TELLUS_TEMP_UPLOAD SET TELLUS_TEMP_UPLOAD.LOC_YEAR = YEAR(NEW_TIME);

Am I going about this in the wrong way? Can the Year function work on a field or only a value?

Thanks for your help!

 

[AlexCuse]

Why do you want to store this information in separate columns?

You can do it, but you want your new columns to be TEXT or INTEGER data type, not DATE.

In 99% of cases you are going to be better off parsing your date in your query, and storing it in one DATE column.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[craigorama]

Hey Alex,

That is the answer, Thanks!

I formatted the loc_year field as an integer instead of date.

The reason i need to split them is because I need to (eventually) insert the data from this table into an existing table that has fields for loc_year, loc_day, and loc_month. O f course, all of those are integers as well.

thanks again.

 

[lespaul]

The reason i need to split them is because I need to (eventually) insert the data from this table into an existing table that has fields for loc_year, loc_day, and loc_month. O f course, all of those are integers as well.

I would have a single date field, MyDateField. In the queries that you use to insert the data I would do:

INSERT INTO newTable (Loc_Year, Loc_Day, Loc_Month) (SELECT Year(MyDateField), Day(MyDateField), Month(MyDateField) FROM MyTableName)

now I don't have the issues of multiple date fields that need to be concatenated together, I have a single date field that I can split up as needed.

Like Alex said:

In 99% of cases you are going to be better off parsing your date in your query (which is what I show above), and storing it in one DATE column.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[craigorama]

Ahh, I see now.

Thanks very much. All is fixed now and I am parsing the data in the insert query.

-cjo