Date string to Date (1 Viewer)

Gismo

Registered User.
Local time
Today, 09:13
Joined
Jun 12, 2017
Messages
636
Hi,

I have a excel spreadsheet with a date in a format of ddmmyy (280912)
i import the spreadsheet into a table but want the field to be imported in format "dd/mm/yy"
How do i do this?
from the append query or after the table has been updated
if from the append query, how do i accomplish this?
 

Ranman256

Registered User.
Local time
Today, 02:13
Joined
Apr 9, 2015
Messages
3,640
I always LINK my xl sheets as external linked tables.
then to import , run an append query, The query can convert the field:
mid([dteFld],4,2) & "/" & left([dteFld],2) & "/" & right([dteFld],2)
 

Gismo

Registered User.
Local time
Today, 09:13
Joined
Jun 12, 2017
Messages
636
i tried the above string but in a format dd/mm/yy

Left([Installation Date (DDMMYY)],2) & "/" & Mid([Installation Date (DDMMYY)],3,2) & "/" & Right([Installation Date (DDMMYY)],2)
does not give the required result
250918 = 25/0918/19

also, i have an issue with a date such as:
11012 which should then be 01/12/12
if i use left 2 but there is no 01, the result is not what it should be
 

Minty

AWF VIP
Local time
Today, 07:13
Joined
Jul 26, 2013
Messages
7,516
I'm not sure your example is correct. 11012 would probably be 1/10/12 ?

If your dates are really formatted ddmmyy then 01/12/12 would be 011212 ?

Does excel recognise the field as a date?
 

namliam

The Mailman - AWF VIP
Local time
Today, 08:13
Joined
Aug 11, 2003
Messages
11,680
i tried the above string but in a format dd/mm/yy

Left([Installation Date (DDMMYY)],2) & "/" & Mid([Installation Date (DDMMYY)],3,2) & "/" & Right([Installation Date (DDMMYY)],2)
does not give the required result
250918 = 25/0918/19

also, i have an issue with a date such as:
11012 which should then be 01/12/12
if i use left 2 but there is no 01, the result is not what it should be
250918 = 25/0918/19
Doesnt make sence! and quite honestly is impossible to boot.

11012 is possible to fix, however how does your number date work in may? 1512 or 10512?

The best way to handle this situation is to make a "tblDate" that holds your numeric date in one column and the "real date" in the other.

If 11012 is your only "problem" you can fix it by prefixing your dates with a 0:
Format([Installation Date (DDMMYY)], "000000")
and then using your existing formatting, please DO NOTE !!! the resulting date needs to be formatted as "MM/DD/YY" otherwize 011012 will become 10 Jan, instead of 1 okt.

If you need to be able to handle 1512, it becomes more complicated and you may want/need to resort to a UDF.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:13
Joined
Feb 19, 2002
Messages
30,098
Gismo, you didn't do what ranman suggested. you "Interpreted". When you work with a date as a string in SQL, the string MUST be either mm/dd/yyyy format or the unambiguous yyyy/mm/dd.

SQL server needs to "interpret" dates like 10/01/20. Is this the 10th of January or the 1st of October? SQL Server ASSUMES mm/dd/yyyy for ambiguous string dates.

Format does not come into play when working with dates that are not strings.
 

Gismo

Registered User.
Local time
Today, 09:13
Joined
Jun 12, 2017
Messages
636
Gismo, you didn't do what ranman suggested. you "Interpreted". When you work with a date as a string in SQL, the string MUST be either mm/dd/yyyy format or the unambiguous yyyy/mm/dd.

SQL server needs to "interpret" dates like 10/01/20. Is this the 10th of January or the 1st of October? SQL Server ASSUMES mm/dd/yyyy for ambiguous string dates.

Format does not come into play when working with dates that are not strings.
i think i understand what you are saying

i tried the mm/dd/yy as advised but still get incorrect results

301119 = 30 Nov 2019

mid is not giving me the required result
result should be 11

1603440546388.png


Expr4: Mid([Installation Date1],3,2) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)
 
Last edited:

arnelgp

error reading drive A:
Local time
Today, 15:13
Joined
May 7, 2009
Messages
10,858
format the Excel column As Short Date.
import as normal.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
7,145
I cannot believe that expression produces what you show :(
Code:
tt="301119"
? mid(tt,3,2) & "/" & left(tt,2) & "/" & right(tt,2)
11/30/19
That expression is more like
Code:
Expr4: Mid([Installation Date1],3,2) & Right([Installation Date1],2) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)
 

Minty

AWF VIP
Local time
Today, 07:13
Joined
Jul 26, 2013
Messages
7,516
I actually think your source data is not reliable for anything to provide a definitive method for this to work.

What date is 31119

is it 31/Jan/2019 ?
or 3/Nov/2019 ?

How would you know?
EDIT: And 301119 as a number (Which is how Excel and Access actually store dates) translates to 07/06/2724
 

Gismo

Registered User.
Local time
Today, 09:13
Joined
Jun 12, 2017
Messages
636
I cannot believe that expression produces what you show :(
Code:
tt="301119"
? mid(tt,3,2) & "/" & left(tt,2) & "/" & right(tt,2)
11/30/19
That expression is more like
Code:
Expr4: Mid([Installation Date1],3,2) & Right([Installation Date1],2) & "/" & Left([Installation Date1],2) & "/" & Right([Installation Date1],2)
I tried numerous options and codes
 

Gismo

Registered User.
Local time
Today, 09:13
Joined
Jun 12, 2017
Messages
636
I actually think your source data is not reliable for anything to provide a definitive method for this to work.

What date is 31119

is it 31/Jan/2019 ?
or 3/Nov/2019 ?

How would you know?
sorry, finger trouble
should be 301119
which translates in 30 Nov 2019

the spreadsheet heading indicates DDMMYY
 

Minty

AWF VIP
Local time
Today, 07:13
Joined
Jul 26, 2013
Messages
7,516
sorry, finger trouble
should be 301119
which translates in 30 Nov 2019

the spreadsheet heading indicates DDMMYY

No not finger trouble I was highlighting a conundrum in your data. I think you need to clarify exactly the format actually used, as it obviously isn't DDMMYY it looks more like
DMMYY

E.g the day part doesn't have a leading zero. Which then asks the question does the same apply for the month section as well?

So is the 1st of January in the data saved as

a) 1120
b) 10120
c) 010120

If it's a) you have a problem Huston! as there are dates in that format that are ambiguous.

If it's b) then a function would sort it out.

if it's c) then the mid / left /right functions will work.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:13
Joined
Sep 21, 2011
Messages
7,145
sorry, finger trouble
should be 301119
which translates in 30 Nov 2019

the spreadsheet heading indicates DDMMYY
The spreadsheet heading can say anything :(
What do Day(cell), Month(cell) and Year(cell) indicate in the worksheet?
 

Gismo

Registered User.
Local time
Today, 09:13
Joined
Jun 12, 2017
Messages
636
No not finger trouble I was highlighting a conundrum in your data. I think you need to clarify exactly the format actually used, as it obviously isn't DDMMYY it looks more like
DMMYY

E.g the day part doesn't have a leading zero. Which then asks the question does the same apply for the month section as well?

So is the 1st of January in the data saved as

a) 1120
b) 10120
c) 010120

If it's a) you have a problem Huston! as there are dates in that format that are ambiguous.

If it's b) then a function would sort it out.

if it's c) then the mid / left /right functions will work.
you are correct

it is in fact DMMYY (10120)
i converted my data in access table to "000000"
i now have 010120

the data in excel is in D-M-Y format as indicated in the spreadsheet headed

1603453993910.png



so option 3 should be the one i fall in

not sure if i know how to use the left function because i dont get only the first 2 digits from the string when using, 1,2

1603454769739.png
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:13
Joined
Feb 19, 2002
Messages
30,098
maybe you could post the actual spreadsheet. Your post #7 should have worked.
 

Users who are viewing this thread

Top Bottom