Remove Time from a Date Field

[horatiu00]

Hello Guys,

I have a field in a DB formatted as date. Some dates are containing the TIME and some aren't. I would live to remove the time from the ones who has it. Could you pls support with a solution?

 

[Joe4]

You should be able to use the DateValue function to do that.
See: DateValue Function

 

[horatiu00]

I don't know how to use it... [ITSM Asset Load Template].Accepted is the fields from which i want to delete the time. Below my sql code:

SELECT [ITSM Asset Load Template].Account, [ITSM Asset Load Template].[Assigned to], [ITSM Asset Load Template].Location, [ITSM Asset Load Template].[Model Number], [ITSM Asset Load Template].[Serial number], [ITSM Asset Load Template].Stockroom, [ITSM Asset Load Template].[Acceptance document], [ITSM Asset Load Template].Accepted, [ITSM Asset Load Template].[Acquisition method(acquisition_method)], [ITSM Asset Load Template].[Added to contract by], [ITSM Asset Load Template].[Asset State], [ITSM Asset Load Template].Substate, [ITSM Asset Load Template].[Asset tag], [ITSM Asset Load Template].[Business Type], [ITSM Asset Load Template].Configuration, [ITSM Asset Load Template].[Cost center], [ITSM Asset Load Template].[Customer Period Service Price], [ITSM Asset Load Template].[Customer Period Service Price Currency], [ITSM Asset Load Template].[Customer Period HW Price], [ITSM Asset Load Template].[Cust Period HW Price Currency], [ITSM Asset Load Template].[Customer order number], [ITSM Asset Load Template].[Delivery document], [ITSM Asset Load Template].Department, [ITSM Asset Load Template].[Disposal reason], [ITSM Asset Load Template].[EarlyTerm Price], [ITSM Asset Load Template].[EarlyTerm Price currency], [ITSM Asset Load Template].[Effective date], [ITSM Asset Load Template].[Exclude from billing], [ITSM Asset Load Template].[Fleet Flex], [ITSM Asset Load Template].[HP Order number], [ITSM Asset Load Template].Installed, [ITSM Asset Load Template].[HW Invoice Number], [ITSM Asset Load Template].[Lease Buyout Cost/FMV], [ITSM Asset Load Template].[Lease Buyout Cost/FMV currency], [ITSM Asset Load Template].[Lease Coordinator], [ITSM Asset Load Template].[Lease End (date)], [ITSM Asset Load Template].[Lease Start (date)], [ITSM Asset Load Template].[Leasing Schedule Number], [ITSM Asset Load Template].[Leasing status], [ITSM Asset Load Template].[Linked to contract], [ITSM Asset Load Template].[Logistics Cost], [ITSM Asset Load Template].[Logistics Cost currency], [ITSM Asset Load Template].[MAC Address], [ITSM Asset Load Template].[Order type], [ITSM Asset Load Template].Ordered, [ITSM Asset Load Template].[Original support start], [ITSM Asset Load Template].Parent, [ITSM Asset Load Template].[Period HW cost], [ITSM Asset Load Template].[Period HW cost currency], [ITSM Asset Load Template].[Period service cost], [ITSM Asset Load Template].[Period service cost currency], [ITSM Asset Load Template].[POD (Date)], [ITSM Asset Load Template].[Retired date], [ITSM Asset Load Template].[Scheduled retirement], [ITSM Asset Load Template].[Shipped date], [ITSM Asset Load Template].[Source document], [ITSM Asset Load Template].[Support agreement], [ITSM Asset Load Template].[Swap/Loaner Asset], [ITSM Asset Load Template].[Override Term], [ITSM Asset Load Template].Term, [ITSM Asset Load Template].[Warranty expiration], [ITSM Asset Load Template].[Variance fee], [ITSM Asset Load Template].[Variance fee Currency], [ITSM Asset Load Template].[Override Currency], [ITSM Asset Load Template].[Location Detail], [ITSM Asset Load Template].[Zip code], [ITSM Asset Load Template].[Big deal Numbers], [ITSM Asset Load Template].[Customer Buyout Price], [ITSM Asset Load Template].[Customer Buyout Price currency], [ITSM Asset Load Template].[Lease EarlyTerm Cost], [ITSM Asset Load Template].[Lease EarlyTerm Cost Currency], [ITSM Asset Load Template].[Ordered By], [ITSM Asset Load Template].[Customer Request Date], [ITSM Asset Load Template].[Custom Field 1], [ITSM Asset Load Template].[Custom Field 2], [ITSM Asset Load Template].[Custom Field 3], [ITSM Asset Load Template].[Custom Field 4], [ITSM Asset Load Template].[Custom Field 5], [ITSM Asset Load Template].[Custom Field 6], [ITSM Asset Load Template].[Custom Field 7], [ITSM Asset Load Template].[Custom Field 8], [ITSM Asset Load Template].[Custom Field 9], [ITSM Asset Load Template].[Custom Field 10], [ITSM Asset Load Template].[Old serial number], [ITSM Asset Load Template].[New Serial number], [ITSM Asset Load Template].[Contract Currency Code], [ITSM Asset Load Template].[Contract Exchange Rate], [ITSM Asset Load Template].[Device Purchase Price/Bundle Price], [ITSM Asset Load Template].[Device Purchase Price/Bundle Price currency], [ITSM Asset Load Template].Localization, [ITSM Asset Load Template].[BIOS Version], [ITSM Asset Load Template].[Country of Origin Name], [ITSM Asset Load Template].[HDD Serial], [ITSM Asset Load Template].[Image Version], [ITSM Asset Load Template].IMEI, [ITSM Asset Load Template].[Security CIS], [ITSM Asset Load Template].[SSD Serial], [ITSM Asset Load Template].[HW Invoice Date], [ITSM Asset Load Template].[Under Dispute], [ITSM Asset Load Template].Version_20220505
FROM [ITSM Asset Load Template]
WHERE ((([ITSM Asset Load Template].[Asset tag]) Like "*MB18*"));

 

[Joe4]

Simply replace:

[ITSM Asset Load Template].Accepted

in your SQL code with:

DateValue([ITSM Asset Load Template].Accepted)