Splitting Date from Time in Access Project

[Guest]

I'm working with a former developer's SQL Server database using an adp. For
unknown reasons, the developer chose to concatenate date & time in the Date
field of a critical table. My query needs to only pull out the Date part and
discard time, but I can't find a function that will work for this. DATEPART
would be proper but none of the intervals I'm aware of will pull it out in
MM/DD/YYYY format.

Any ideas?

Randall Arnold

 

[John Spencer]

Not sure if it is available in an ADP, but have you tried DateValue?

 

[Guest]

Unfortunately that won't work in an adp, but thanks for the suggestion.

Randall Arnold

 

[Duane Hookom]

The date only part:
Convert(DateTime,Convert(VarChar(11),DateTimeField))

If you have to do this quite often, consider creating your own function that
is the equivalent of DateValue().

-- Function to remove the time value from a date and time
Create Function DateValue
(@DateAndTime datetime)
Returns DateTime
AS
BEGIN
Return Convert( DateTime,Convert(VarChar(11), @DateAndTime) )
END

You can then use the function in your SQL like
dbo.DateValue(DateAndTimeField)

 

[Guest]

Thanks Duane. Hopefully I'll only have to do this once...

Randall Arnold