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