[AccessD] Convert SQL Server Date/Time to Date

MartyConnelly martyconnelly at shaw.ca
Fri May 19 12:04:30 CDT 2006


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.
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada




More information about the AccessD mailing list