×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Change char value to date

thread183-1803628

Change char value to date

Change char value to date

(OP)
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)

RE: Change char value to date

This may help.
Otherwise try this pc2


---- Andy

There is a great need for a sarcasm font.

RE: Change char value to date

(OP)
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

RE: Change char value to date

(OP)
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?

RE: Change char value to date

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


---- Andy

There is a great need for a sarcasm font.

RE: Change char value to date

(OP)
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

CODE --> 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'

RE: Change char value to date

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 …
CONVERT(varchar(10), orderheader.DateTimeCreated, 103) >= '2020/06/15'
and CONVERT(varchar(10), orderheader.DateTimeCreated, 103) <= '2020/06/16'



---- Andy

There is a great need for a sarcasm font.

RE: Change char value to date

(OP)
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).

CODE --> 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

RE: Change char value to date

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.

RE: Change char value to date

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…

Quote:

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 ...

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close