Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

date Format

Quote Reply
date Format
Hi all!

Just upped to SQL, and I am a whole lot over my head! Started with Import.pl, but I had changed the date format (To mmmm dd yyyy) in 2.0, and I need to make that change in SQL. WHERE in SQL is the format set????

Thanks!

Dave
Quote Reply
Re: date Format In reply to
As i understand the SQl and LinksSql right its not an easy thing to change the time-stamp; so think about exporting your old database into excel sort it for date and set the column to the right format (if there are many links it could go with a makro, easier i think mark all from one month and set it all to one day; or is it so important to have the certain date?) Then export it back to ascii. (As i remember i do always the way text to Access, Access to Excel and the whole way back. With this way i could do 30.000 Entrees from a Access-DB to the right format for links (filling contact-name with 'a', mail to a@a.de and so on) Shurely there is an easier way, but sometimes someone must improvisate (right expression?)
Quote Reply
Re: date Format In reply to
Hello Robert!

It is NOT necessary to import from Access to Excel for the purpose. I have been able to manage without it. I also know that for any date format it is possible.

You just import from ASCII to Access and after working and polishing import directly into MySQL and thats it. When I started, I did not have enough support but somehow I have managed.

All the missing features have been truely an extremely frustrating experience. Those features seems to be quite a necessary part of Links SQL and are not custom requirements as claimed unfortunately by other Links SQL users. Further problems I had were, rather severe, during imports. Let me know if you face as well. I will explain how I went about them.

To change the date format, Alex has explained how to do this in the other thread. In the last five minites I could not find my thread myself. So I leave it to you. You need to insert this lines in the editor.cgi and it will do correct. If you cannot find it let me know and I will post it here. Its very very easy. During imports, you only need to specify in the sub_routine what will be incoming format. The output format will be in every case the same.
Quote Reply
Re: date Format In reply to
Hi:

I found Alex's previous note, and it said to add a su routine and call it from various places. Unfortunately, it does not go into detail about what that sub routine should be, where to put it, and where it needs to be called from.

I would assume the database would continue to be in YYYY-MM-DD format, but it would be modded for display purposes, right? (Since, in my reading, I am under the impression that the YYYY-MM-DD is the only format mySQL undertands. And since date will be a sort field, I do not want to mess SQL up, and I do not want to just convert it to TEXT.... I want it to have the date properties.)


If you have a working sub-routine, and know where specifically it should be called from, I would be grateful if you would post that.... seems like that would be a good candidate for the resource forum!

Thank you!

Dave

Quote Reply
Re: date Format In reply to
What I would do is in HTML_Templates.pm add:

sub convert_date {
# ---------------------------------------
my $in = shift;
my %months = qw!1 January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11 November 12 December!;
my ($year, $mon, $day) = split /-/, $in;
return "$months{int $mon} $day $year";
}

and then in site_html_link try:

$rec->{Disp_Date} = &convert_date ($rec->{Add_Date});

and then use <%Disp_Date%> in your template.

Hope this helps,

Alex
Quote Reply
Re: date Format In reply to
Alex:

Thanks so much!

Dave
Quote Reply
Re: date Format In reply to
Alex:

Whoops! Forgot an important point! I need the date in the detail page, which you may remember from a previous e-mail, is generated "on-the-fly". I tried just popping this code into the generate_detailed_page of page.cgi, and it did not work. What do I need to do to get it there, please?

Dave
Quote Reply
Re: date Format In reply to
Hello!

To add, while I was fishing on the internet, I found out a plugin for export/import. I may have to look somewhere what was the plugin that changed directly and worked with Access superb.

In the message above, you do not seem to use Access and so I beleive you do not need it and could happiliy use what Alex said above (Which is ofcourse much better as you could also use it in the templates!). However if you need it, I could post it here Smile
Quote Reply
Re: date Format In reply to
rajani:

Thanks for the reply, but that is not where this problem lies. I am just attempting to change the way the data is displayed by SQL in a tempolate, not actually CHANGE the format in the database. Appreciate the offer, but not what I needed>

OK, I copied the sub-routine Alex put out for HTML_Templates.pm into page.cgi, and then added:

$rec->{Disp_Date} = &convert_date ($rec->{Add_Date});

into sub generate_detailed_page, but this causes errors in page.cgi. Obviously, there needs to be some sort of tweek to the call to the subroutine, or the subroutine itself to run in page.cgi. Unfortunately, this is WAY over my head... anyone got an idea what would make this run in page.cgi for detailed pages?

Thanks!

Dave
Quote Reply
Re: date Format In reply to
What sort of errors do you get? If it's a 500 server error, what do you get if you type perl -c page.cgi from telnet?

Cheers,

Alex
Quote Reply
Re: date Format In reply to
Alex:

My fault! I am sometimes a bit slow- sorry!

It should NOT be copied to page.cgi, but leave the sub routine exactly where Alex said. Then just put the:

$rec->{Disp_Date} = &convert_date ($rec->{Add_Date});

In the sub_build_pagexxxx in HTML_Templates.pm that you want the convert to appear in. Why I thought I needed to move it to page.cgi I will never know!

Sorry Alex! My fault- your code runs BEAUTIFULLY!

Dave

Quote Reply
Re: date Format In reply to
Would you please be more specific where i should insert

$rec->{Disp_Date} = &convert_date ($rec->{Add_Date});


Thank you, Robert
Quote Reply
Re: date Format In reply to
Robert:L

Not a problem. I wanted mmmm dd,yyyy in my detail pages. here is how that build section looks now:

# Set new and pop to either 1 or undef for templates.
($rec->{'isNew'} eq 'Yes') ? ($rec->{'isNew'} = 1) : (delete $rec->{'isNew'});
($rec->{'isChanged'} eq 'Yes') ? ($rec->{'isChanged'} = 1) : (delete $rec->{'isChanged'});
($rec->{'isPopular'} eq 'Yes') ? ($rec->{'isPopular'} = 1) : (delete $rec->{'isPopular'});
$rec->{disp_date} = &convert_date ($rec->{Add_Date});
defined $dynamic and &load_user ($dynamic, $tags);
my $output = &load_template ('detailed.html', {
%$tags,
%$rec,
%GLOBALS
}, undef, $template );
defined $dynamic and &clean_output($dynamic, \$output);
return $output;
}

Seemed like a good place to add it in....

Good Luck!

Dave

Quote Reply
Re: date Format In reply to
Robert:

Also, keep in mind it does not change the date format of the database, it only changes the way it looks in that page...

The date in SQL is still yyyy-mm-dd.

Dave
Quote Reply
Re: date Format In reply to
Yeah, thats my thought, just only to change the output:-)

Just in this sec i found the place for it.Thank you
Quote Reply
Re: date Format In reply to
Ok, we have done the date-format for the links so far; but how can i change the one at the home.html-template.

It should be in words:
get the actuell date with:

xxxxxx \&Links: BSQL::get_date;

then call the

&convert_date ();

My problem: How can i do that, which vars i should use?

Robert


Quote Reply
Re: date Format In reply to
When I put changes in the HTML_Templates.pm I can no longer build. Where should I be putting the sub convert_date in?

Thanks
Rennie
Quote Reply
Re: date Format In reply to
What errors are you getting?

The most common is forgetting to declare any new variables with "my"

Another is to forget the ';' at the end.

Your server error log will let you know.



------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/







Quote Reply
Re: date Format In reply to
Thanks, The coding was all correct because I just cut and pasted it.

I tried again and it worked the next time.

Rennie