Parker Software Ltd Homepage
Forum Home Forum Home > ThinkAutomation Business Process Automation > ThinkAutomation Version 4
  New Posts New Posts RSS Feed - Convert excel date/time value to SQL date/time fmt
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Convert excel date/time value to SQL date/time fmt

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

Joined: 22 Jul 2014
Location: NH
Points: 28
Post Options Post Options   Thanks (0) Thanks(0)   Quote GenTent Quote  Post ReplyReply Direct Link To This Post Topic: Convert excel date/time value to SQL date/time fmt
    Posted: 31 Jul 2019 at 1:10pm
I have some data to suck in from Excel that is in excel's date/time decimal format.  The target is a SQL field in datetime format.  What's the trick to convert this format?  I have tried every data type in the extracted field - string and text provide the decimal value as characters, date or date&time pass extract null, integer/decimal pass the value in number format. None of these can be used with the trigger action for converting dates.  

Is there a trick to doing this or do I need to write a VB script as part of extracting the field? 

thanks
Mark
thanks,
Mark Carpenter
www.gentent.com
WeaterProof Your Power!
Back to Top
parkersoft-nick View Drop Down
Admin Group
Admin Group
Avatar
Lead Developer - ThinkAutomation

Joined: 28 Dec 2015
Location: UK
Points: 130
Post Options Post Options   Thanks (0) Thanks(0)   Quote parkersoft-nick Quote  Post ReplyReply Direct Link To This Post Posted: 01 Aug 2019 at 10:07am
Hi Mark,

It seems that Excel is defaulting your date to the amount of days since  1st Jan 1900.

You could create an extraction script to add the days on to this date and then convert that to your preferred date method as per my example below:

Example: If the date pulled from Excel was 43665

Dim Val As Date
Val = DateAdd("d", 43665, "1900-01-01")
ThinkAutomationExtract = Format(Val, "YYY-MM-DD")

The result in my tests for the above converted 43665 to "7/21/2019"

Hope this helps.

Thanks,
Nick



Edited by parkersoft-nick - 01 Aug 2019 at 10:07am
Nick Proud - Parker Software

Software Development Lead -ThinkAutomation
Back to Top
GenTent View Drop Down
New User
New User
Avatar

Joined: 22 Jul 2014
Location: NH
Points: 28
Post Options Post Options   Thanks (0) Thanks(0)   Quote GenTent Quote  Post ReplyReply Direct Link To This Post Posted: 01 Aug 2019 at 10:49am
Thank you Nick, I'll pursue that path!
thanks,
Mark Carpenter
www.gentent.com
WeaterProof Your Power!
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.05
Copyright ©2001-2016 Web Wiz Ltd.

This page was generated in 0.078 seconds.