[AccessD] Convert SQL Server Date/Time to Date

Gustav Brock Gustav at cactus.dk
Sat May 20 08:34:22 CDT 2006


Hi Marty

Yes, that's why the minimum date in SQL Server is 1753-01-01.
I guess it's inherited from the original Sybase code.

Access dates go back to 100-01-01.
But dates in VBA follow a pseudo Gregorian calender from year 100 to the introduction of the Gregorian calendar in October 1582:

  http://www.ghgrb.ch/genealogicalIntroduction/kalender_gregorianisch.html 

where it started replacing the Julian calender and simply cut out the 11 days between the 4. and the 15. of October. VBA does not correct for this.
Note that the World's move to the Gregorian calendar was a very long process lasting about 340 years. Russia delayed the switch until the time of the revolution, and the last country, Turkey, switched as late as in 1923:

  http://www.ghgrb.ch/genealogicalIntroduction/kalender_greg_start_chron.html 

/gustav

>>> martyconnelly at shaw.ca 19-05-2006 19:04 >>>
Ah, but then there is the Year 1752 bug at least for British dates.
and its resultant 10 lost days.

The best known case in the U.S. is that of George Washington, whose
birthday was for many years a national holiday.  It was observed on
February 22, which is when Washington observed it--after 1752.  He
was born 1732-02-11 (O.S.).

A further complication for dates in England and her colonies is that
the official year began in March, so that Washington's birthday would
have been written Feb. 11, 1732/31 (O.S.).

This supposed birthdate for Issac Newton (4 Jan 1642) is a cute example
of calendar confusion.  Newton was born on Christmas, 25 Dec 1642.
Or, at least, that's what day it was in England. Across the
Channel in France,  that same day was 4 Jan 1643; because France
had switched to the Gregorian calendar, which England did not
adopt until 1752.  The confusion here presumably came from switching to
the modern calendar date but trying to keep the familiar year of birth.

Heenan, Lambert wrote:

>Very true. I my normal daily life I never need to work with dates earlier
>that 1900, but other do for sure.
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
>Sent: Friday, May 19, 2006 10:02 AM
>To: accessd at databaseadvisors.com 
>Subject: Re: [AccessD] Convert SQL Server Date/Time to Date
>
>
>Hi Lambert
>
>Just a note of caution. 
>You must use Fix() if you expect negative date values (prior to 1899-12-30)
>
>  CDate(Fix(SomeDateField))
>
>and/or - if you expect Null values (empty date field) - good old CVDate():
>
>  CVDate(Fix(SomeDateField))
>
>/gustav
>
>  
>
>>>>Lambert.Heenan at AIG.com 19-05-2006 15:21:26 >>>
>>>>        
>>>>
>In Access land, using Format will give you a *String* that looks like a
>date. If you want an actual Date type try...
>
>	Cdate(Int(SomeDateField))
>
>Using Int() strips off the decimal fraction, which is the time of day, and
>the remaining integer value is then converted to a real date type.
>
>HTH
>
>Lambert
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
>Sent: Thursday, May 18, 2006 5:58 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Convert SQL Server Date/Time to Date
>
>
>Hi Chester
>
>Assume your Date Field is called 'SomeDateField
>(I'm an Aussie so we order our dates as Date/Month/Year)
>
>In Access...
>	Format(Me.SomeDAteField, "dd/mm/yyyy)
>In SQL
>	convert (char(10), SomeTableName.SomeDateField, 103)  AS 'SomeDate' 
>
>Hope this helps
>
>Have a great day
> 
>Darren
>------------------------------
>T: 0424 696 433
> 
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
>Sent: Friday, 19 May 2006 7:49 AM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Convert SQL Server Date/Time to Date
>
>I have an access database linked to an SQL server table. SQL server stores a
>time with the date. I want to extract only the date part when using this
>field in a query. I know I have done it before but can someone refresh my
>memory. Thanks.




More information about the AccessD mailing list