[AccessD] Migrating date fields to SQL Server

Charlotte Foust charlotte.foust at gmail.com
Mon Jun 19 10:08:07 CDT 2017


Thanks Arthur.  Unfortunately, this is a State computer and I'm not allowed
to install tools like that on it.

Charlotte Foust
916-206-4336

On Jun 19, 2017 2:35 AM, "Arthur Fuller" <fuller.artful at gmail.com> wrote:

> The Australian software firm SSW provides a tool designed for this type of
> migration, and takes care of the various issues involving dates. It's free.
>
> Arthur
>
> On Mon, Jun 19, 2017 at 2:19 AM, Gustav Brock <gustav at cactus.dk> wrote:
>
> > Hi Charlotte
> >
> > You must be thinking of the numeric zero value which for VBA equals
> > 1899-12-30 while it for T-SQL is 1900-01-01.
> >
> > However, you should only in rare cases deal with the numeric value of a
> > date/time. Date and time should always be handled like dates, not
> strings,
> > not numbers, no exceptions really.
> >
> > The ODBC drivers know all about this. The current issue you may
> experience
> > is, that the old ODBC drivers don't convert DateTime2 of T-SQL to
> DateTime
> > but to Text. So, either use DateTime in your SQL tables or use the newer
> > SQL Native drivers of version 11 or later. And never use Date, Time, or
> > SmallDateTime as data type in your SQL Server fields.
> >
> > /gustav
> >
> > -----Oprindelig meddelelse-----
> > Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af
> > Charlotte Foust
> > Sendt: 19. juni 2017 06:18
> > Til: Access Developers discussion and problem <
> > accessd at databaseadvisors.com>
> > Emne: [AccessD] Migrating date fields to SQL Server
> >
> > Nearly a decade ago when I was working in .Net and SQL, I knew how to
> > migrate Access tables into SQL Server and handle the date field issues
> that
> > arose.  I've forgotten it all, and I need to find a resource to refresh
> my
> > memory and  help me do what needs doing.  I know it has to do with the
> > differences between the dates in Access and SQL Server but I can't
> remember
> > why it should be a problem moving the tables from Access with a shorter
> > date/time to SQL with a longer one.  And I haven't a clue as to the
> > mechanics of getting around the issue, aside from writing scripts, which
> I
> > really would like to avoid.
> >
> > Can anyone shed some light on this for me?
> >
> >
> > Charlotte Foust
> > (916) 206-4336
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
>
> --
> Arthur
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list