Date conversions

I have to import data from an ASCII text file and
use INSERT INTO ... to create records in an existing
table.

There are a couple of fields with dates in the form
Mar 05,10

I know that I can set the 9 characters into a string and
manipulate the string using format(... to massage the data
into a form suitable for the SQL string using #s.

I was just wondering if that technique is the most efficient
or is there a better approach?

-- 
Len
______________________________________________________
remove nothing for valid email address. 


0
Len
3/10/2010 1:18:19 AM
access 16762 articles. 3 followers. Follow

8 Replies
547 Views

Similar Articles

[PageSpeed] 44

On Wed, 10 Mar 2010 11:18:19 +1000, ".Len B" <gonehome@internode0.on0.net>
wrote:

>I have to import data from an ASCII text file and
>use INSERT INTO ... to create records in an existing
>table.
>
>There are a couple of fields with dates in the form
>Mar 05,10
>
>I know that I can set the 9 characters into a string and
>manipulate the string using format(... to massage the data
>into a form suitable for the SQL string using #s.
>
>I was just wondering if that technique is the most efficient
>or is there a better approach?

Only a little massage is needed:

?cdate("Mar 05,10")
3/5/2010 


-- 

             John W. Vinson [MVP]
0
John
3/10/2010 1:37:13 AM
Thanks John
That sure beats slicing and reassembling the string myself
which was what I had in mind.

I just knew there would be something better.

-- 
Len
______________________________________________________
remove nothing for valid email address.
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:jttdp5dvaa4u50aokehgh5iaicmtntgfpm@4ax.com...
| On Wed, 10 Mar 2010 11:18:19 +1000, ".Len B" 
<gonehome@internode0.on0.net>
| wrote:
|
| >I have to import data from an ASCII text file and
| >use INSERT INTO ... to create records in an existing
| >table.
| >
| >There are a couple of fields with dates in the form
| >Mar 05,10
| >
| >I know that I can set the 9 characters into a string and
| >manipulate the string using format(... to massage the data
| >into a form suitable for the SQL string using #s.
| >
| >I was just wondering if that technique is the most efficient
| >or is there a better approach?
|
| Only a little massage is needed:
|
| ?cdate("Mar 05,10")
| 3/5/2010
|
|
| -- 
|
|             John W. Vinson [MVP]
|



0
Len
3/10/2010 1:45:42 AM
On Wed, 10 Mar 2010 11:18:19 +1000, ".Len B"
<gonehome@internode0.on0.net> wrote:

That actually is a valid date, so no conversion would be needed. Type
this in the Immediate window:
?IsDate("Mar 05,10")
True

-Tom.
Microsoft Access MVP


>I have to import data from an ASCII text file and
>use INSERT INTO ... to create records in an existing
>table.
>
>There are a couple of fields with dates in the form
>Mar 05,10
>
>I know that I can set the 9 characters into a string and
>manipulate the string using format(... to massage the data
>into a form suitable for the SQL string using #s.
>
>I was just wondering if that technique is the most efficient
>or is there a better approach?
0
Tom
3/10/2010 2:15:19 AM
Thanks Tom,
John's suggestion didn't work as presented. I had to surround the
result with # characters to make it acceptable in the VALUES clause
of the INSERT INTO".

I expect yours will behave similarly. Both generate a True in IsDate.

I dare say that if I were to change my regional settings then I
wouldn't need any #.
-- 
Len
______________________________________________________
remove nothing for valid email address.
"Tom van Stiphout" <tom7744.no.spam@cox.net> wrote in message 
news:440ep51ovouoh4fuvvl7gsrs4ru4f5f3u8@4ax.com...
| On Wed, 10 Mar 2010 11:18:19 +1000, ".Len B"
| <gonehome@internode0.on0.net> wrote:
|
| That actually is a valid date, so no conversion would be needed. Type
| this in the Immediate window:
| ?IsDate("Mar 05,10")
| True
|
| -Tom.
| Microsoft Access MVP
|
|
| >I have to import data from an ASCII text file and
| >use INSERT INTO ... to create records in an existing
| >table.
| >
| >There are a couple of fields with dates in the form
| >Mar 05,10
| >
| >I know that I can set the 9 characters into a string and
| >manipulate the string using format(... to massage the data
| >into a form suitable for the SQL string using #s.
| >
| >I was just wondering if that technique is the most efficient
| >or is there a better approach?



0
Len
3/10/2010 8:27:18 AM
On Wed, 10 Mar 2010 18:27:18 +1000, ".Len B" <gonehome@internode0.on0.net>
wrote:

>I dare say that if I were to change my regional settings then I
>wouldn't need any #.

Nope. An INSERT INTO a date/time field needs # delimiters. The format is
irrelevant.
-- 

             John W. Vinson [MVP]
0
John
3/10/2010 5:00:50 PM
".Len B" <gonehome@internode0.on0.net> wrote in message 
news:uinNU%23%23vKHA.5940@TK2MSFTNGP02.phx.gbl...
>I have to import data from an ASCII text file and
> use INSERT INTO ... to create records in an existing
> table.
>
> There are a couple of fields with dates in the form
> Mar 05,10
>
> I know that I can set the 9 characters into a string and
> manipulate the string using format(... to massage the data
> into a form suitable for the SQL string using #s.
>
> I was just wondering if that technique is the most efficient
> or is there a better approach?
>
> -- 
> Len
> ______________________________________________________
> remove nothing for valid email address.
>
> 

0
De
3/11/2010 4:02:29 PM
live me a lone
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:jttdp5dvaa4u50aokehgh5iaicmtntgfpm@4ax.com...
> On Wed, 10 Mar 2010 11:18:19 +1000, ".Len B" <gonehome@internode0.on0.net>
> wrote:
>
>>I have to import data from an ASCII text file and
>>use INSERT INTO ... to create records in an existing
>>table.
>>
>>There are a couple of fields with dates in the form
>>Mar 05,10
>>
>>I know that I can set the 9 characters into a string and
>>manipulate the string using format(... to massage the data
>>into a form suitable for the SQL string using #s.
>>
>>I was just wondering if that technique is the most efficient
>>or is there a better approach?
>
> Only a little massage is needed:
>
> ?cdate("Mar 05,10")
> 3/5/2010
>
>
> -- 
>
>             John W. Vinson [MVP] 

0
sonia
3/14/2010 4:29:19 AM
�*^*



".Len B" <gonehome@internode0.on0.net> a �crit dans le message de groupe de 
discussion : uinNU##vKHA.5940@TK2MSFTNGP02.phx.gbl...
> I have to import data from an ASCII text file and
> use INSERT INTO ... to create records in an existing
> table.
>
> There are a couple of fields with dates in the form
> Mar 05,10
>
> I know that I can set the 9 characters into a string and
> manipulate the string using format(... to massage the data
> into a form suitable for the SQL string using #s.
>
> I was just wondering if that technique is the most efficient
> or is there a better approach?
>
> -- 
> Len
> ______________________________________________________
> remove nothing for valid email address.
>
> 
0
joelgeraldine
3/17/2010 1:35:12 PM
Reply:

Similar Artilces:

Investment Account Summary for date in the past
I can see the total quantity of shares I currently own for each investment on the Account Summary view. How do I find out how many shares I owned on a particular date in the past? In microsoft.public.money, jch wrote: >I can see the total quantity of shares I currently own for each investment on >the Account Summary view. How do I find out how many shares I owned on a >particular date in the past? Have you tried the AsOf date on the Portfolio view? (upper right) No, I did not see that. Thank you. "Cal Learner-- MVP" wrote: > In microsoft.public.money, jch wro...

Show the max value of a column according to a given date and show its definition
Given: For Sheet1: A1: 11/2/04 (This is the current date) A4: Project Code A5: Dent A6: Stain B4: 11/3/04 B5: 10 B6: 5 C4: 11/4/04 C5: 12 C6: 20 Question: How can I have the maximum number value of the current dat displayed in A1? Desired Solution: A15: (this cell will display the max value of the column given by th current date.) B15: (this cell will display the Reason Code of the max value of th current date) Logic: If the date in A1 is changed, the value of A15 and B15 will b changed as well regarding to the current date in A --...

Concatinating two dates.....?
I have two cells that display dates. 1/25/2004 (B16) and 2/7/2004 (N22). I want to combine the values in one cell formatted like this, "1/25/2004 - 2/7/2004". This formula "=B16&" - "&N22" returns "38011 - 38024" What's the answer? What am I missing? Thanks Kelvin Hi try =TEXT(B16,"MM/DD/YYYY") & " - " & TEXT(N22,"MM/DD/YYYY") >-----Original Message----- >I have two cells that display dates. 1/25/2004 (B16) and 2/7/2004 (N22). >I want to combine the values in one cell formatted like t...

Printing Cheques in Dynamics 9.0 With New Canadain Date Format
I have been trying to print cheques in Dynamics 9.0 by modifing the cheque report so it prints according to the new CPA date format. I have had no luck in doing this. I see in Canadain payroll with the new tax tables there is something that touches on the new date format. We do not use the canadian payroll module so this seems to be of no help to use. Has anyone been printing to the new date format? if so how? Thank you in advance. I've done it in Dynamics 8.0. Couldn't bother to wait for Microsoft. They promised it in July, 2006. They haven't noticed that it's Aug...

Count dates until complete or Cancel
(Excel 2003) Is there a way to calculate the days and then stop the counting when you have entered a completion date and/or cancelled appears in the "Status column? I want the duration column to keep calculating the days until either a date is enter into the "Completion Date" Column or the "Status" Column has cancelled or completed in it. Start Date Completion Date Status Duration 3/24/10 9 3/15/10 3/20/10 Cancelled 5 -- ...

Date formatting problems with hlookup
I have had this kind of problem before and just don't understand the problem. I have a couple of input cells that are date formatted. Then, I have a bunch of hlookup functions that find that date in a row of dates and read down N rows to find a value. I am getting N/A's right now and cannot figure out the problem. All dates have, at least now, the same formatting (*3/14/2001), so there should be no confusion as to it being a number. I have tried other ones such as xx/mm/yyyy, also to no avail. Sometimes it seems that, if I start from scratch and rewriter the equations, it fixes i...

Outlook 97 dates
Every message I received is dated the first day of the month no matter what the day is when displayed in "in box". Any help out there? ...

Showing Date Criteria On Report
Thanks to steve who gave me the criteria for my query which enable my report to Automatically Filter projects that that week, what i would like to also do is have a unbound text box which display these date how do i do this? This is the Criteria i used Between "DateAdd("d",-4,Date()) And Date()" Under ReciveciedDate field. cant i just use something like this on my report =Between &DateAdd("d",-4,Date()) And Date() ="Between " & DateAdd("d",-4,Date()) & " And " & Date() -- Doug Steele, Microsoft Access MVP http:...

Selecting a date value from two fields
I have a table with two date fields: one an unconfirmed date scheduled upto 3 years in advance, and a confirmed date which is entered nearer the appointment time. I need to be able to run a report showing either: the unconfirmed date if no confirmed date is entered, or the confirmed date if it is available. I need to keep the two dates as I also need to show how many appointments were done early or late, compared to the advance-scheduled date. How do I do this. I have struggled through and can't make it work. In query design, enter an expression like this: TheDate: IIf([ConfirmedDate] Is...

date and time picker control in excel vba
I created a program in excel 2003 using the Date and Time picker control. I now have Excel 2007 and do not have the control any more. I can't find it in the addtional controls. Is there a place where I can down load the control? Date/Time Picker and MonthView Control are part of a group of ActiveX controls MSCOMCT2.OCX. You can download this from the below location http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB -- Jacob "davwork" wrote: > I created a program in excel 2003 using the Date and Time picker control. I > now have Excel 2007 and d...

Date of email in History
We would like to see the date an email was sent/received after it is attached to a case. Is this possible? Only the body of the email is shown. Thanks, Stacy -- Stacy Hi Stacy Yes, you will need to modify the email form and views in the customization tools in CRM to display the date. If you are still on CRM 3.0, You may also want to contact microsoft support to get the hotfix that will use the date that the email was received in Outlook as oppossed to the date that was when the email was tracked. Hope this helps! -- Nick Doelman www.readybms.com "srd" wrote: > W...

Displaying Date in Textbox?
How do I display the current date in a Textbox? I've tried to link it to a cell but end up it expressed in numerica format eg 37956.6999259259. I'd like it to be displayed in either o the following formats dd/mm/yy or dd/mmm/yyyy ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Alan, What you are seeing is the value that Excel stores for a date, which is a serial numbe...

urgent date format problem help needed....
Hi, I have a vb.net application that is inserting data into a sql table. one of the cols in my table, is of type date. My sql statement is insert into tbl_opsJournal (article,action,quantity,container,status,job_number,date) values ('R5W','-',6,'00101','00','123','26/3/2010 13:41:00') The insert fails with The conversion of a carchar data type to a datetime data type resulted in an out-of-range value... If I change the date to be 3/26/2010.... then it works... Looking at the control panel, the regional options is set...

Automate Change of the format of a field to short date for export
Hi, I have a table that I am creating through a make table query. Once created, I want to export the table to a text file and save to a location. I am attempting to create a macro that will do this. One of my fields is called CMMonth. The problem is that when I export this table to a text file, it is exporting this field as a long date with hours:minutes:seconds. The system I am importing the text file to rejects the long date. I can only be M/D/Y. I have tried changing the field format manually once the table is created but it still exports it as a long date. How can I c...

Bill Payment Service Update/Bill Payment Conversion
I'm having a little trouble updating my online accounts with Microsoft Money. I use MS Money to process bill payments through CommerceOnline Bank and also to automatically download transactions from CommerceOnline. I've been doing this now for over a year and I've never had any problems, but for some reason, I haven't been able to update my accounts for the last several weeks. If I go to CommerceOnline myself, I can still manually download transactions. It also appears that the Bill Pay transactions that I recently submitted through MS Money have been processed corre...

When I enter numbers, they show up as dates
I need to enter the following value: 1-99 Whever i enter it, excel changes it to Jan-99. I have tried formatting the cell to the general format, different number formats, etc., but the same thing keeps happening. I do not want to change the format to "text" in case i need to make graphs/charts from the data. Any help would be much appreciated. Thank you! Precede them with an apostrophe ' or preformat the cell as text, This helpfulness from Excel has always annoyed me guessing that anything with a hyphen or a slash must be a date. -- Regards, Peo Sjoblom "hung...

Concatenated Dates?
Hi, I'm trying to solve a problem whereby dates are being transferred to their 1900 system value when I don't want them to. Let's say we have the following cells: A1 contains 01-Mar-06 [01/03/2006 with custom format dd-mmm-yy] A2 contains 15-Jun-06 [15/06/2006 with custom format dd-mmm-yy] A3 contains =A1&" to "&A2 A3 is a simple concatenation, with the intention of returning: 01-Mar-06 to 15-Jun-06 However, it gives: 38777 to 38883 So, how can I get the concatenation to use the formatted date & not the 1900 system value? I've tried all ...

Can I Count dates without repeating?
I am trying to count dates column without repeating every date twice. For Example: 08/10/05 08/10/05 08/11/05 08/11/05 08/11/05 Answer should be 2. Is it possible in Excel? Thank you for your time -- bogdan7 ----------------------------------------------------------------------- bogdan77's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2620 View this thread: http://www.excelforum.com/showthread.php?threadid=39511 This is an array formula, committed with Ctrol-Shift-Enter. Assumes you dates are in cells A1:A10 =SUM(1/COUNTIF(A1:A10,A1:A10)) "bogdan...

Date range calc
Hi I was wondering if anyone could help with this problem i am having? I have spreadsheet that has a list of companies names and in the next colum where I put the date in which it was recieved. I have 12 sheet for 12 months. For examaple when I get a report in december from one of the companies I enter it in I enter the date it was recieved. Once I enter the date. I want it to count that it has been recieved for December. If the cell is blank I want it to count has not recieved. The formula i have tried is as follows =countif(A1:A4, Between "01/12/03 and 31/12/03") Pleas...

Date format #20
Every time I change the x and y ranges on ANY line graph generated from an excel file the date format on ALL open files and ALL files opened subsequently is changed from dd/mm/yyyy to dd/mm/yy. Although this is not a permanent change (the format is correct on re-starting Excel) it is annoying. Can anyone please suggest why this is happening and if it can be remedied? Thanks. Bill Ridgeway What version of Excel (2007 or earlier)? The problem is just with one file or any file that you make a chart? When you 'change the ranges', what actions do you perform? What you are obse...

Template workbook forgets dates for x axis
I have set up a workbook with the design that data is input (pasted) in 8 columns: date, time, and 6 x 'data point' columns. A calculation sheet does some work on the data and charts on different sheets look at the calculation sheet for data. The workbook is supposed to be a master with no data and the charts looking at blank cells until data is input. Then a copy is saved for ongoing use with all charts set up and displaying the correct info without user interaction. The calculation sheet combines the input date and time to display "dd/mm/yyyy hh:mm"(with a formula...

Convert Date/Time to the Hour
Is there a formula to convert this 1/3/2005 11:39:02 AM to this 11:39 AM. I have formatted the cells but that merely changes what the user will see and not the true value of the cells. One way: =ROUND(MOD(A1,1)*1440,0)/1440 Format as hh:mm AM/PM In article <673EBB31-14A9-460B-9910-2DAD874DA6AD@microsoft.com>, "eric_rs1" <ericrs1@discussions.microsoft.com> wrote: > Is there a formula to convert this 1/3/2005 11:39:02 AM to this 11:39 AM. I > have formatted the cells but that merely changes what the user will see and > not the tru...

Unable to format dates!
I am an Excel 2000 user. When I attempt to display dates in a range in a similar way, some change takes place but some are left unchanged, and the sample within the format cells/number dialog box does not display the different types of date when selected, is there a download to upgrade this error? Thanks in advance douglas Hi Douglas, Your "dates" are probably text, rather than real Excel dates. This often happens when downloaded from websites. Format an empty cell as number, Edit>Copy, select your "dates", Edit>Paste Special, check "add" -- Kind R...

Daily data to weekly data conversion in Excel
Daily data to weekly data conversion in Excel Kindly tell me a simple WAY to do it. Actually i am having daily stock market prices, i just want a simple way to transfer them into weekly prices. Say row A has dates and Row B has values. Not quite sure what you want here but I am sending my file to get historical quotes. GetYahooMultipleHistoryALL_P PS. I will respond to other requests made to me but NOT to other requests made on the ng. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Poorak Kashyap" <poorakk@yahoo.com> wrote in mess...

How do I insert an Auto Date in Excel
I would like to insert an auto date in a form and not in the header/footer. Is this possible to do and if so, could you please provide me with instructions Your could put =TODAY() in a cell and it will update everyday, if you want to put in a date that want change you can use a macro with some code like this, maybe in the open or before print event Sheets("Sheet1").Range("A1") = Date -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using...