Seperating a Timestamp

[EAB1977]

I am hoping someone has had some experience here.

In my query, I need to seperate a Timestamp into two different fields;
a date fiend and a time field. I am successful in seperating the
fields, although they are now strings instead of Date and Time fields.
When I try to query on the date fields, I do not get the data I desire
since they are now string fields.

Is there anyway around this?

SELECT dbo_vwPlant.Name, dbo_Shipment.ID, Sum(dbo_Sleeve.Samples) AS
NumOfSets, Format([StartDate],"mm/dd/yyyy") AS StartedDate,
Format([StartDate],"hh:mm") AS StartedTime, Format([CompletedDate],"mm/
dd/yyyy") AS CompleteDate, Format([CompletedDate],"hh:mm") AS
CompletedTime, dbo_Shipment.AssignedStaffID
FROM dbo_vwPlant INNER JOIN (dbo_Shipment INNER JOIN dbo_Sleeve ON
dbo_Shipment.Key = dbo_Sleeve.ShipmentKey) ON dbo_vwPlant.Code =
dbo_Shipment.PlantCode
WHERE (((dbo_Shipment.ProductLineCode)="0EPS") AND
((dbo_Shipment.OnDemandTestID)=0))
GROUP BY dbo_vwPlant.Name, dbo_Shipment.ID, Format([StartDate],"mm/dd/
yyyy"), Format([StartDate],"hh:mm"), Format([CompletedDate],"mm/dd/
yyyy"), Format([CompletedDate],"hh:mm"), dbo_Shipment.AssignedStaffID,
dbo_Shipment.AssignedStaffID, dbo_Shipment.ID
ORDER BY dbo_Shipment.AssignedStaffID, dbo_Shipment.ID;

 

[Dale Fye]

Try using the DateValue and TimeValue functions. These retain the date/time
data type rather than using the Format command, which converts the output to
a string.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[John Spencer]

Try using DateValue and TimeValue functions. Or use Cdate to force conversion
of your strings.

In either case you are going to need to make sure you are passing the
functions a valid date time string.

IIF(ISDate(StartDate),DateValue(StartDate),Null) as StartedDate
IIF(ISDate(StartDate),TimeValue(StartDate),Null) as StartedDate


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dick D]

I hope this doesn't sound to simplisitic, but
use the same field twice in your form or report,
use formatting to display the elements dd/mm/yy for date
hh:nn AMPM for
time.

 

[John Spencer]

An alternative is to retain the fields as datetime fields and NOT manipulate
them. Apply your criteria against the fields in their "raw" state - this will
normally be faster and can be much faster with large data sets.

You can also include the calculated fields in the query (if you wish) or wait
until you need to display them and then do the formatting - using either the
DateValue and TimeValue functions Or the format functions.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Try using DateValue and TimeValue functions. Or use Cdate to force
conversion of your strings.

In either case you are going to need to make sure you are passing the
functions a valid date time string.

IIF(ISDate(StartDate),DateValue(StartDate),Null) as StartedDate
IIF(ISDate(StartDate),TimeValue(StartDate),Null) as StartedDate


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am hoping someone has had some experience here.

In my query, I need to seperate a Timestamp into two different fields;
a date fiend and a time field. I am successful in seperating the
fields, although they are now strings instead of Date and Time fields.
When I try to query on the date fields, I do not get the data I desire
since they are now string fields.

Is there anyway around this?

SELECT dbo_vwPlant.Name, dbo_Shipment.ID, Sum(dbo_Sleeve.Samples) AS
NumOfSets, Format([StartDate],"mm/dd/yyyy") AS StartedDate,
Format([StartDate],"hh:mm") AS StartedTime, Format([CompletedDate],"mm/
dd/yyyy") AS CompleteDate, Format([CompletedDate],"hh:mm") AS
CompletedTime, dbo_Shipment.AssignedStaffID
FROM dbo_vwPlant INNER JOIN (dbo_Shipment INNER JOIN dbo_Sleeve ON
dbo_Shipment.Key = dbo_Sleeve.ShipmentKey) ON dbo_vwPlant.Code =
dbo_Shipment.PlantCode
WHERE (((dbo_Shipment.ProductLineCode)="0EPS") AND
((dbo_Shipment.OnDemandTestID)=0))
GROUP BY dbo_vwPlant.Name, dbo_Shipment.ID, Format([StartDate],"mm/dd/
yyyy"), Format([StartDate],"hh:mm"), Format([CompletedDate],"mm/dd/
yyyy"), Format([CompletedDate],"hh:mm"), dbo_Shipment.AssignedStaffID,
dbo_Shipment.AssignedStaffID, dbo_Shipment.ID
ORDER BY dbo_Shipment.AssignedStaffID, dbo_Shipment.ID;