Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Change char value to date

Status
Not open for further replies.
Cpreston

Cpreston

MIS
Mar 4, 2015
987
GB
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)
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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?

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


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
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

SQL: 
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'

 
Upvote 0 Downvote
You refer to your original field (DateTimeCreated) in your WHERE portion of your Select which is a character field (according to you):
Code: 
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:
Code: 
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.
 
Upvote 0 Downvote
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).

SQL: 
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
 
Upvote 0 Downvote
Here you don't refer to any Date field in your WHERE portion of your Select statement... [ponder]


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
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)
Click to expand...

In this case in order to get ALL of 03/06/2020 you need to do this ...
Code: 
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,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bharons
  • Locked
  • Question
Pick up value from combobox
Replies
0
Views
590
bharons
bharons
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
601
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How te filter by date ?
Replies
9
Views
580
dhookom
dhookom
ahmedsa2018
  • Locked
  • Question
How to change STRING_AGG to stuff xml to split feature value by pip?
Replies
0
Views
309
ahmedsa2018
ahmedsa2018

Part and Inventory Search

Sponsor

Back
Top