question

EriKDavila-1048 avatar image
0 Votes"
EriKDavila-1048 asked ·

help in date format

CAST(DATEADD(HOUR,-6,j.JobStarted) AS varchar) AS 'DelWindowStart', how can I format the date for only to have the 02/11/21 Date, month, and year

right now is Feb 1 2021 1:01 PM like this

sql-server-generalsql-server-transact-sql
· 4
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

1 Vote 1 ·

@EriKDavila-1048

Although the question is unclear. But, As far as I can understand, you just want to convert the datetime - 'Feb 1 2021 1:01 PM' to '02/11/21' format?

You can use 'FORMAT' function : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh213505(v=sql.110)

DECLARE @date datetime2 = 'Feb 1 2021 1:01 PM';
SELECT FORMAT( @date, 'dd/MM/yy', 'en-US' ) AS 'Result';

Hope this helps.

0 Votes 0 ·

@EriKDavila-1048,

You never provided a minimal reproducible example: ##1-4.
Please provide, by adding all four bullets to the original question.
Otherwise, you are wasting valuable time of folks who are trying to help you.

0 Votes 0 ·

Do you have any update?

0 Votes 0 ·
EriKDavila-1048 avatar image
0 Votes"
EriKDavila-1048 answered ·

will this work as this is the actual query

SELECT o.OrderID,
o.ExternalOrderID,
TRIM(ISNULL(o.CustFn, '') + ' ' + ISNULL(o.CustLN, '')) as CustomerName,
p.Name as PartnerName,
CASE
WHEN (o.BrandName IS NOT NULL)
THEN o.BrandName
ELSE s.StoreName
END AS StoreName,
a.AreaName,
o.ZoneName,
o.PickupAddress,
o.MerchantZipCode as 'PickupZip',
o.DropoffAddress,
CAST(DATEADD(HOUR,-6,j.JobStarted) AS varchar) AS 'DelWindowStart', this are the one that I need to get them correct for me as 02/01/21
CAST(DATEADD(HOUR,-6,j.JobEnded) AS varchar) AS 'DelWindowEnd',
this are the one that I need to get them correct for me as 02/01/21
CAST(DATEADD(HOUR,-6,o.DriverArriveTime) AS varchar) as 'DriverArriveTime',
DATEDIFF(minute, j.JobStarted, o.DriverArriveTime) as 'ArrivalDrift',
CAST(DATEADD(HOUR,-6,o.DriverOutForDeliveryTime) AS varchar) AS 'PickTime', --CASE WHEN o.DriverOutForDeliveryTime IS Not NULL THEN o.DriverOutForDeliveryTime ELSE jo.PickTime END AS 'PickTime' ,
DATEDIFF(minute, o.DriverArriveTime, o.DriverOutForDeliveryTime) as 'DriverWaitTime',
CAST(DATEADD(HOUR,-6,jo.DropTime) AS varchar) as DropTime,
DATEDIFF(minute, j.JobEnded, jo.DropTime) as 'DeliveryDrift',
j.Status,
ROUND(ISNULL(((SELECT TotalDistance From Jobs Where JobId = j.JobId) / 1609.344),0),2) as 'TotalMiles',
ROUND(ISNULL(j.MileageRate, 0),2) as 'MileageRate',
(ROUND(ISNULL(((SELECT TotalDistance From Jobs Where JobId = j.JobId) / 1609.344),0),2) * ROUND(ISNULL(j.MileageRate, 0),2)) as 'TotalMileageRate',
ROUND(ISNULL(j.BaseRate, 0),2) as 'BaseRate',
ROUND(ISNULL(j.GuaranteeRate, 0),2) as 'GuaranteeRate',
ROUND(ISNULL(j.DeliveryFee, 0),2) as 'DeliveryFee',
ROUND(ISNULL(j.MileageFee, 0),2) as 'MileageFee',
ROUND(ISNULL(j.OtherFee, 0),2) as 'OtherFee',
ROUND(ISNULL(j.ReturnFee, 0),2) as 'ReturnFee',
ROUND(ISNULL(j.PeakHourRate, 0),2) as 'PeakHourRate',
ROUND(ISNULL(j.CancellationFee, 0),2) as 'CancellationFee',
ROUND(ISNULL(j.ExtraRate, 0),2) as 'Tips',
ROUND(ISNULL((select sum(ot.CalculatedTip) from OrderTips as ot where ot.Orderid = o.OrderId), 0.00),2) as 'PostTips',
convert(varchar(50),jo.DelayTime) as DelayTime,
jo.DelayReason,
CASE
WHEN o.IsOrderReturned=1 THEN 'True'
ELSE 'False'
END as IsOrderReturned,
CASE
WHEN o.ActualDeliveryTime IS NULL THEN 'No Info'
WHEN o.ActualDeliveryTime > o.DelWindowEnd THEN 'Late'
WHEN o.ActualDeliveryTime < o.DelWindowStart THEN 'Early'
WHEN o.ActualDeliveryTime BETWEEN o.DelWindowStart AND o.DelWindowEnd THEN 'Ontime'
ELSE 'Other'
END AS OnTimeDeliveryFlag,
o.IsSkipcartCancelled as 'IsAdminCanceled',
o.IsRetailerCancelled as 'IsRetailerCancelled',
o.DescriptionOfRetailerCancel as 'OrderStateAtRetailerCancel',
(SELECT top 1 ExceptionalEvent FROM JobEvents WHERE OrderId = O.OrderID and EventName = 'close_order') as 'ReasonCode',
TRIM(ISNULL(d.FirstName, '') + ' ' + ISNULL(d.LastName, '')) as DriverName,
-- o.NoOfBags,
-- jo.NoOfBags as NoOfBagsPicked,
-- o.OrderStatus
ISNULL(o.BillingDeliveryFee,0) as 'BillingDeliveryFee',
ISNULL(o.BillingReturnFee,0) as 'BillingReturnFee',
ISNULL(o.BillingCancellationFee,0) as 'BillingCancellationFee',
ISNULL(o.BillingMileageFee,0) as 'BillingMileageFee',
ISNULL(o.BillingTip,0) as 'BillingTip'
FROM Orders o WITH (NOLOCK)
LEFT JOIN JobOrders jo WITH (NOLOCK) ON o.OrderID = jo.OrderId and jo.IsArchived = 0
LEFT JOIN Jobs j WITH (NOLOCK) ON jo.JobId = j.JobId
LEFT JOIN Drivers d ON J.DriverId = d.DriverId
LEFT JOIN Store s WITH (NOLOCK) ON s.StoreId = o.StoreID
LEFT JOIN Partner p WITH (NOLOCK) ON p.PartnerId = o.PartnerId
LEFT JOIN Regions r WITH (NOLOCK) ON o.RegionId = r.RegionId
LEFT JOIN OrderArea oa WITH (NOLOCK) ON o.OrderID = oa.OrderId
LEFT JOIN Areas a WITH (NOLOCK) ON oa.AreaId = a.AreaId
WHERE o.DelWindowStart BETWEEN '02/01/2021'and '02/28/2021'
and (j.Status ='cancelled' or j.Status='delivered')
GROUP BY o.OrderID,
o.ExternalOrderID,
o.CustFn,
o.CustLN,
a.AreaName,
o.ZoneName,
o.PickupAddress,
o.MerchantZipCode,
o.DropoffAddress,
j.JobStarted,
j.JobEnded,
s.StoreName,
-- o.NoOfBags,
-- jo.NoOfBags,
--- o.OrderStatus,
jo.DelayReason,
o.IsOrderReturned,
j.Status,
jo.PickTime,
jo.DropTime,

 jo.DelayTime, 
 j.JobId, 
 j.MileageFee, 
 j.BaseRate, 
 j.GuaranteeRate, 
 j.DeliveryFee, 
 j.OtherFee, 
 j.ReturnFee, 
 j.PeakHourRate,
 j.CancellationFee, 
 j.ExtraRate, 
 o.Tip, 
 d.FirstName, 
 d.LastName, 
 j.MileageRate, 
 p.Name, 
 o.BrandName, 
 o.IsSkipcartCancelled, 
 o.DriverArriveTime, 
 o.DriverOutForDeliveryTime,
 o.ActualDeliveryTime, 
 o.DelWindowStart, 
 o.DelWindowEnd, 
 o.IsRetailerCancelled, 
 o.DescriptionOfRetailerCancel,
 o.ExternalOrderID, 
 o.BillingDeliveryFee, 
 o.BillingReturnFee, 
 o.BillingCancellationFee,
 o.BillingMileageFee,
 o.BillingTip

ORDER BY p.Name, DelWindowStart










· 6 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@EriKDavila-1048

You can use :

Format(CAST(DATEADD(HOUR,-6,@date) AS datetime),'MM/dd/yy', 'en-US' ) as 'DelWindowStart'
and same for the other.

Please update if that works.


0 Votes 0 ·

![73040-image.png][1]



it works but the date of the month is 00

0 Votes 0 ·
image.png (15.3 KiB)

@EriKDavila-1048

cannot test with your data, but as I with my test date it is working.
Nonetheless, try using without culture i.e. remove 'en-us'.

else,
re cast the datetime part as date : Format(CAST(CAST(DATEADD(HOUR,-6,@date) AS datetime) as date),'MM/dd/yy')



0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·
convert(char(8), DATEADD(HOUR,-6,j.JobStarted, 3)


However, I would give this a second thought. The best is to return date and time values to the client, and let the client format according to the user's regional settings.

By the way, don't use NOLOCK if you don't understand exactly what it does. With NOLOCK you may read uncommitted and inconsistent data. You may also fail to read data that has been committed for a long time.

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

well the formula is still not working is there a way you can make the statement and take the cast away and find another solution

and regarding the NOLOCk, I don't have any idea what is that

0 Votes 0 ·

well the formula is still not working is there a way you can make the statement and take the cast away and find another solution

I see now that there is a syntax error in my post; a parenthesis is missing. It should be:

 convert(char(8), DATEADD(HOUR,-6,j.JobStarted), 3)

3 is the specific format code for the format you want.

But as I said, you should probably do this in the client in stead.

and regarding the NOLOCk, I don't have any idea what is that

So why on Earth do you use something you don't know what it is? Remove it!

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

Hi @EriKDavila-1048

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)? So that we’ll get a right direction and make some test.

Please provide a minimal example of data.

Echo

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ·

Other option is to use the CONVERT function, here you can pass a format parameter, e.g. 101 for US format, see CAST and CONVERT (Transact-SQL) =>
Date and Time styles

 select convert(varchar(10), getdate(), 101)


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

JobStarted and JobEnded are obviously not datetime data types. I assume they are varchars. Without sample data and the format of the value in the varchar field, it is difficult to help you with your problem.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.