Re: Seperating a Timestamp

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

EAB1977 wrote:
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;
.

Relevant Pages

  • Re: Long sqls create ""Command contains unrecognised phrase/keyword"
    ... FROM studentsstatus; ... into cursor cur_temp " ... INNER JOIN status ON status.id = studentsstatus.statusid " ... You need to break your string up into concatenated smaller strings ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Seperating a Timestamp
    ... You can also include the calculated fields in the query 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 wrote: ... a date fiend and a time field. ... although they are now strings instead of Date and Time fields. ...
    (microsoft.public.access.queries)
  • sorry once more in english: sql : little differences big effects ??
    ... Although I tested these strings in ACCESS without using delphi I would like to ask if someone understands why these two apparently similar strings give very different results: ... field 0.name "PCon" contains name of compound eg CompNaCl ... PConProduct PConReactant Coefficient ... FROM public_Reactions AS C INNER JOIN ...
    (borland.public.delphi.database.ado)
  • SQL Strings kleine Unterschiede --grosse Wirkung ??
    ... Although I tested these strings in ACCESS without using delphi I would like to ask if someone understands why these two apparently similar strings give very different results: ... field 0.name "PCon" contains name of compound eg CompNaCl ... PConProduct PConReactant Coefficient ... FROM public_Reactions AS C INNER JOIN ...
    (borland.public.delphi.database.ado)
  • Re: Union query
    ... start time field, finish time field. ... "John Vinson" wrote: ... strings. ... No date/time value will ever be greater than or equal to the ...
    (microsoft.public.access.queries)