Change char value to date

[Cpreston]

Hi

I have a line of code which I need to change to date, Could someone advise ASAP, I have tried to change as char top as Date but that does not work, Thanks

CAST(CONVERT (varchar(10), orderheader.DateTimeCreated, 103) AS char)

 

[Andrzejek]

This may help.
Otherwise try this


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

I did this and got the date back, however I am trying to use msquery so I can select between 2 dates, but if I put in between [02/06/2020) AND [08/06/2020] I am getting dated from outside the range.
If I put in [02/06/2020) AND [06/06/2020] it brings back it correctly. I cant seem to choose between 2 different dates. I know msquery is not sql hence I thought it was the field not converted correctly.

CONVERT(varchar(10), orderheader.DateTimeCreated, 103) AS createddate

 

[Cpreston]

Sorry on my last reply I put If I put in [02/06/2020) AND [06/06/2020] it brings back it correctly. I meant If I put in [02/06/2020) AND [02/06/2020] it brings back just data for that date.
It wont let me select between 2 dates and result back correctly. I know this is not msquery forum , but I thought it could be the sql converting wrong?

 

[Andrzejek]

Could you show your full Select statement with the dates selected?


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Yes I can , it is a very large statement though and is used in a bigger report, as you can see I am only interested in 2 subgroups. I am using msquery to get the results into excel for pivot use, but as I say the dates are not working on createdate field

SELECT        TOP (100) PERCENT CASE WHEN '[148-vwProductGroupSubGroup].Group' = 'ArborFlor' AND [148-vwProductGroupSubGroup].SubGroup = 'Hardwood flooring' THEN 99 ELSE [Order] END AS [Order], 
                         orderline.OrderID, orderheader.OrderNumber, orderheader.DateRequired AS DeliveryDate, orderline.ProductID, product.ProductGroupID, dbo.[148-vwProductGroupSubgroup].[Group ID] AS ParentID, 
                         CASE WHEN salesRep.Name LIKE 'Louis%' THEN 2205 ELSE orderheader.SalesRepID END AS SalesRepID, CASE WHEN salesRep.Name LIKE 'Louis%' THEN 'Louis Howarth' ELSE ISNULL(salesrep.Name, 
                         'Unknown') END AS RepName, orderline.Quantity, orderline.TotalVolume, orderline.InputPerID, per.PerCode, dbo.[148-vwProductGroupSubgroup].[Group] AS ProdGroupName, 
                         dbo.[148-vwProductGroupSubgroup].SubGroup AS SubGroupName, 
						 CONVERT(varchar(10), orderheader.DateTimeCreated, 103) AS createddate, 
						 ---CAST(CONVERT(varchar(10), orderheader.DateTimeCreated, 103) AS date) AS createddate, 
						 orderheader.OrderType, 
                         orderheader.DateTimeCreated, orderheader.BranchID, orderheader.Deleted, orderheader.OrderStatus
FROM            dbo.Per AS per RIGHT OUTER JOIN
                         dbo.OrderLine AS orderline INNER JOIN
                         dbo.OrderHeader AS orderheader ON orderheader.OrderID = orderline.OrderID INNER JOIN
                         dbo.Product AS product ON product.ProductID = orderline.ProductID INNER JOIN
                         dbo.[148-vwProductGroupSubgroup] ON product.ProductID = dbo.[148-vwProductGroupSubgroup].ProductID INNER JOIN
                         dbo.[148-GroupOrder] ON dbo.[148-vwProductGroupSubgroup].[Group] = dbo.[148-GroupOrder].[Group] ON per.PerID = orderline.InputPerID LEFT OUTER JOIN
                         dbo.SalesRep AS salesrep ON salesrep.SalesRepID = orderheader.SalesRepID
						 --Where  [148-vwProductGroupSubgroup].SubGroup like 'Treated' OR  [148-vwProductGroupSubgroup].SubGroup LIKE 'Reg_________measure'
						 Where  [Product].[ProductGroupID] = 2070 OR [Product].productgroupid = 2037 AND
						-- [orderheader].DateTimeCreated     >= '2020-06-15 00:00:00' and [orderheader].DateTimeCreated   <= '2020-06-16 00:00:00'

 

[Andrzejek]

You refer to your original field (DateTimeCreated) in your WHERE portion of your Select which is a character field (according to you):

Select …
CONVERT(varchar(10), orderheader.DateTimeCreated, 103) AS createddate
From…
Where …
[orderheader].DateTimeCreated     >= '2020-06-15 00:00:00' and [orderheader].DateTimeCreated   <= '2020-06-16 00:00:00'

Try something like:

Select …
CONVERT(varchar(10), orderheader.DateTimeCreated, 103) AS createddate
From…
Where …
[blue]CONVERT(varchar(10), orderheader.DateTimeCreated, 103)[/blue] >= '2020/06/15'
and [blue]CONVERT(varchar(10), orderheader.DateTimeCreated, 103)[/blue] <= '2020/06/16'

---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Sorry the date I was just trying to get between 2 dates, attached is the code as it is now. I am using Msquery to draw this from a view and then into Excel using the createdate in msquery fto get between dates. As I said it works great if one date is input but then does not work if I put a range in. I can use the datetime field but then if I do 2 days it misses the second date out (exapled between 02/06/2020 and 03/06/2020), so I have to put in between 3 dates to get a result (02/06/2020 and 04/06/2020).

SELECT        TOP (100) PERCENT CASE WHEN '[148-vwProductGroupSubGroup].Group' = 'ArborFlor' AND [148-vwProductGroupSubGroup].SubGroup = 'Hardwood flooring' THEN 99 ELSE [Order] END AS [Order], 
                         orderline.OrderID, orderheader.OrderNumber, orderheader.DateRequired AS DeliveryDate, orderline.ProductID, product.ProductGroupID, dbo.[148-vwProductGroupSubgroup].[Group ID] AS ParentID, 
                         CASE WHEN salesRep.Name LIKE 'Louis%' THEN 2205 ELSE orderheader.SalesRepID END AS SalesRepID, CASE WHEN salesRep.Name LIKE 'Louis%' THEN 'Louis Howarth' ELSE ISNULL(salesrep.Name, 
                         'Unknown') END AS RepName, orderline.Quantity, orderline.TotalVolume, orderline.InputPerID, per.PerCode, dbo.[148-vwProductGroupSubgroup].[Group] AS ProdGroupName, 
                         dbo.[148-vwProductGroupSubgroup].SubGroup AS SubGroupName, 
						 CONVERT(varchar(10), orderheader.DateTimeCreated, 103) AS createddate, 
						 orderheader.OrderType, 
                         orderheader.DateTimeCreated, orderheader.BranchID, orderheader.Deleted, orderheader.OrderStatus
FROM            dbo.Per AS per RIGHT OUTER JOIN
                         dbo.OrderLine AS orderline INNER JOIN
                         dbo.OrderHeader AS orderheader ON orderheader.OrderID = orderline.OrderID INNER JOIN
                         dbo.Product AS product ON product.ProductID = orderline.ProductID INNER JOIN
                         dbo.[148-vwProductGroupSubgroup] ON product.ProductID = dbo.[148-vwProductGroupSubgroup].ProductID INNER JOIN
                         dbo.[148-GroupOrder] ON dbo.[148-vwProductGroupSubgroup].[Group] = dbo.[148-GroupOrder].[Group] ON per.PerID = orderline.InputPerID LEFT OUTER JOIN
                         dbo.SalesRep AS salesrep ON salesrep.SalesRepID = orderheader.SalesRepID
						 --Where  [148-vwProductGroupSubgroup].SubGroup like 'Treated' OR  [148-vwProductGroupSubgroup].SubGroup LIKE 'Reg_________measure'
						 Where  [Product].[ProductGroupID] = 2070 OR [Product].productgroupid = 2037

 

[Andrzejek]

Here you don't refer to any Date field in your WHERE portion of your Select statement...


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

This has been so confused I'm not certain I know the actual question. But I think that this may be the essence of your dilemma…

but then if I do 2 days it misses the second date out (exapled between 02/06/2020 and 03/06/2020)

In this case in order to get ALL of 03/06/2020 you need to do this ...

SomeField >= StartDate And SomeField < EndDate + 1

When you just specify EndDate you must remember that there is a TIME element and all you get is EndDate 00:00:00, but you miss 00:00:01 to 23:59:59. But is you specify < EndDate + 1, you get ALL od EndDate.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein