Parker Software Ltd Homepage
Forum Home Forum Home > Email2DB Email Parser > General
  New Posts New Posts RSS Feed: Format a Date for Inserting
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Format a Date for Inserting

 Post Reply Post Reply
Author
Message
Nexent1 View Drop Down
New User
New User
Avatar

Joined: 24 Jun 2010
Location: Mississauga
Posts: 8
Post Options Post Options   Quote Nexent1 Quote  Post ReplyReply Direct Link To This Post Topic: Format a Date for Inserting
    Posted: 20 Apr 2012 at 8:37pm
We are working with clients that perfer to use specific date formats.  This is usually based on the country as there are different standards used in different countries.  We are trying to add the date format to the database insert string but it is not recognizing the FORMAT or TO_DATE function.
We would like to know what is the recommended approach to specify a date format when inserting a date field in to a datebase?
 


Edited by Nexent1 - 20 Apr 2012 at 8:45pm
Back to Top
Nexent1 View Drop Down
New User
New User
Avatar

Joined: 24 Jun 2010
Location: Mississauga
Posts: 8
Post Options Post Options   Quote Nexent1 Quote  Post ReplyReply Direct Link To This Post Posted: 20 Apr 2012 at 11:19pm
I have tried a different approach to separate out the Day, Month and Year when extracting the date and wanted to try to concatenate them together for the insert.  I am not able to find a Concatenate function that work with this either.  So looking for help on either how to define a date format or concatenate a string so I can concatenate the separate fields together.
Back to Top
Stephen View Drop Down
Admin Group
Admin Group
Avatar

Joined: 21 Oct 2005
Location: Stoke on Trent
Posts: 1415
Post Options Post Options   Quote Stephen Quote  Post ReplyReply Direct Link To This Post Posted: 21 Apr 2012 at 9:19am
Hi,

Unless you are modifying the SQL created by Email2DB there is no need to do anything.

Email2DB uses ADO to insert into the database - date & date/time fields will be inserted as date types not strings.

Steve

Back to Top
Nexent1 View Drop Down
New User
New User
Avatar

Joined: 24 Jun 2010
Location: Mississauga
Posts: 8
Post Options Post Options   Quote Nexent1 Quote  Post ReplyReply Direct Link To This Post Posted: 23 Apr 2012 at 3:22pm
Thank you Stephen,
 
This initial issue is the date format in the email we are parsing is in DD/MM/YY format.  If we just parse the whole field and select Date format in the Field Type it is being interpreted as YYYY-MM-DD.  For example if the date is 24/04/12 the parser intreprets this as 2024-04-12 which is the wrong date.
 
We are trying to work around this but would prefer a simpler method if we could define the date format.
 
Can we define the date format in a Trigger?
 
Regards,
 
Robert
Back to Top
Nexent1 View Drop Down
New User
New User
Avatar

Joined: 24 Jun 2010
Location: Mississauga
Posts: 8
Post Options Post Options   Quote Nexent1 Quote  Post ReplyReply Direct Link To This Post Posted: 24 Apr 2012 at 2:48pm
Based on a chat session with Technical Support the reason for this will be because database stores the date as YYYY-MM-DD format.  If we require the specific date from within the body, as opposed to one of the built-in date fields, then the way to get around this would be to reformat the date via a VBscript.

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.141 seconds.
These are the forums for Parker Software, developers of Live Chat Software: WhosOn and Email Automation Software: Email2DB.