Format a Date for Inserting

Message Share Topic Printable Version Google Delicious Digg StumbleUpon Windows Live Yahoo Bookmarks reddit Facebook MySpace Newsvine Furl Topic Search Topic Options Post Reply Create New Topic

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

Author	Message Share Topic Printable Version Google Delicious Digg StumbleUpon Windows Live Yahoo Bookmarks reddit Facebook MySpace Newsvine Furl Topic Search Topic Options Post Reply Create New Topic
Nexent1 Members Profile Send Private Message Find Members Posts Visit Members Homepage Add to Buddy List New User Joined: 24 Jun 2010 Location: Mississauga Posts: 6	Post Options Post Reply Quote Nexent1 Report Post Quote Reply 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

Nexent1 Members Profile Send Private Message Find Members Posts Visit Members Homepage Add to Buddy List New User Joined: 24 Jun 2010 Location: Mississauga Posts: 6	Post Options Post Reply Quote Nexent1 Report Post Quote Reply 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.

Stephen Members Profile Send Private Message Find Members Posts Visit Members Homepage Add to Buddy List Admin Group Joined: 21 Oct 2005 Location: Stoke on Trent Posts: 1389	Post Options Post Reply Quote Stephen Report Post Quote Reply 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

Nexent1 Members Profile Send Private Message Find Members Posts Visit Members Homepage Add to Buddy List New User Joined: 24 Jun 2010 Location: Mississauga Posts: 6	Post Options Post Reply Quote Nexent1 Report Post Quote Reply 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

Nexent1 Members Profile Send Private Message Find Members Posts Visit Members Homepage Add to Buddy List New User Joined: 24 Jun 2010 Location: Mississauga Posts: 6	Post Options Post Reply Quote Nexent1 Report Post Quote Reply 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.