Format after change field datatype to date

[Porteño]

I imported a table from Excel.

A filed containing value "01-09-2016" was set to short text datatype.

When I go to table->design View and change column to DATE/TIME, the value turns to 1/9/2016, great.

but, when I run this:

 CurrentDb.Execute "ALTER TABLE [mytable] ALTER COLUMN [Startdatum] DATETIME;"

the value in this field turn to 42378

How do I make it look like a proper date programmatically?



EDIT:


I tried to run an update setting the same value in the field but it came out empty.

If I open the table and click on the value, then I can't leave the cell because is an invalid value.

This is pretty odd, doesn't it?


EDIT 2:
I run:
CurrentDb.Execute "UPDATE[table..] SET [Startdatum] = DATEADD('d',[Startdatum], '1899-12-30');"

And nothing changed! the same number!

but verified that this number is correct
SELECT DATEADD('d',42378,'1899-12-30')
results in 1/9/2016

 

[The_Doc_Man]

First, 42378 might be the right date anyway. Dates are shown as timeline distances from a reference date and that number is about right for this date. So the question is, what was the format by which you were viewing that date?

You see, date/time fields in Access are actually a typecast (or if you prefer, alternate interpretation) of a DOUBLE. Depending on how you ask the question, you might see the DOUBLE and you might see the date/time in some format.

Also, is that a native Access table or an SQL Server back-end table? SQL server has more than one date type so we need to clarify the context of your question.

 

[theDBguy]

Hi. I’m just guessing, but that’s probably a proper date already. Behind the scenes, Access stores date values as a number. What we know or see as dates is merely a formatted version of the number. Just to verify, go back to design view, after you did the ALTER TABLE and select a Format for the field.

Edit: Oops, I swear Doc’s response wasn’t there when I started. Sorry for the duplicate information.

 

[Porteño]

Hi, it is plain Access.

And yes, the number is correct. It is not shown as DATE but shown correctly in the calendar when opening the table.

Is there some metadata I can change for proper display?

See table open and design view attached

 

[Porteño]

So the question is, what was the format by which you were viewing that date?

Also, is that a native Access table or an SQL Server back-end table?

Hi, I just double-clicked on table in object panel (All Access Objects, tables).

It just Access imported from Excel and converted by ALTER TABLE, if I converted by design view, everything looks normal.

 

[theDBguy]

Hi, it is plain Access.

And yes, the number is correct. It is not shown as DATE but shown correctly in the calendar when opening the table.

Is there some metadata I can change for proper display?

See table open and design view attached

Hi. What the image you posted doesn’t show is what’s in the Format property of the field. If it’s empty, try selecting one from the dropdown.

 

[The_Doc_Man]

I'm theDBguy on this one. Check for an empty FORMAT property. Note that doing an ALTER TABLE doesn't fill in the format unless you explicitly tell it the format, whereas the manual alteration of formats carries some "baggage" with it that changes the format property. Think of ALTER TABLE as the programmed - but dumbed-down - way of changing table properties. It makes no assumptions. Sometimes, you WANT assumptions.

 

[Porteño]

Yes, thanks, my intuition was in that direction, as I titled this post.

Adding format in design view solved it.

But, how I change that metadata programmatically? this doen's cut it:

CurrentDb.TableDefs(tablename).Fields("Startdatum").Format

EDIT:

I found it

 CurrentDb.Tabledefs("table..").Fields("Startdatum").Properties("Format").Value = "Short Date"

 

[theDBguy]

Yes, thanks, my intuition was in that direction, as I titled this post.

Adding format in design view solved it.

But, how I change that metadata programmatically? this doen's cut it:

CurrentDb.TableDefs(tablename).Fields("Startdatum").Format

EDIT:

I found it

 CurrentDb.Tabledefs("table..").Fields("Startdatum").Properties("Format").Value = "Short Date"

Hi. Glad to hear you got it sorted out, but you might consider what Pat was saying. Good luck with your project.