Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    31

    Unanswered: Convert SQL Dates

    Hi All

    I have one table called tblCustomer
    I have done a SQL select query

    Select SalesDate, OrderNo
    From tblCustomer

    Table 1 is result of SQL
    OrderID is Primary Key
    There are two fields I want to convert to number starting with prefix C on each row then followed by combining SalesDate + OrderNo
    SalesDate OrderNo
    01/1/2014 0678
    12/1/2014 9099
    15/1/2014 8745



    I need to covert this two field into one field which I want it to start with Prefix C AS SalesOrdeNumber column.
    Find below example what I want after completing this exexercse
    Table 2 third column (SalesOrdeNumber) is what I need as result of Concatenation (SalesDate+ OrderNo +C)

    SalesDate OrderNo SalesOrdeNumber
    2014/01/20 0678 C 1401200678
    2013/09/25 9099 C 1309259099
    2013/04/30 8745 C 1304308745

    SalesOrderNumber is made by combining SalesDate +OrderNo Fields (yymmdd+OrderNo+C (as prefix on every row))
    Point to Note
    Date Format on SalesDate Field YYYY-MM-DD
    OrderNo field is maximum digit is 4

    Thanks so much for your help much appreciated.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    Select 
        SalesDate, 
        OrderNo,
        'C' +
        CONVERT(CHAR(6), SalesDate, 12) + 
        RIGHT(REPLICATE('0', 4) + CAST(OrderNo as varchar(4)), 4) as SalesOrdeNumber
    From tblCustomer
    Hope this helps.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Your whole approach to SQL is completely wrong.

    I have one table called tblCustomer*
    The absurd prefix “tbl-” is a design flaw that violates the rule in data modeling that says we do not mix data and meta-data. It is so bad, it has a name – tibbling.

    A table is a set, so its name has to be collective or plural. That means we have “Personnel”, the abstract set, and not a single “Employee” -- unless that is really true!

    [quote] I have done a SQL select query

    SELECT sale_date, order_nbr
    FROM Customers;

    Please notice that I changed the table name and made the column names follow ISO-11179 rules. But this is still garbage. A sale is an entity, a customer is an entity and the have a relationship in the orders. Orders are not part of a customer! I am an author; my books and I are related by “authorship” and because they grow out of my body.

    “order_nbr” is Primary Key*
    Yes, in the Orders table. Not Customers. Where is the DDL for all of these implied tables?

    There are two fields [sic] I want to convert to number starting with prefix C on each row then followed by combining sale_date + order_nbr
    Another fundamental error! Fields and columns are totally different concepts. Why do you want to increase redundancy?

    The only data display format in ANSI/ISO Standard SQL is ISO-8601. This “yyyy-mm-dd” and not whatever dialect you are using. Dashes and not slashes.

    I need to convert these two fields [sic] into one field [sic] which I want it to start with Prefix C AS sales_order_nbr column.
    WHY?? There is nothing wrong with a multi-column key in RDBMS. The goal of any database is to reduce redundancy and you increase it. This is a display issue and should be done in the presentation layer.

    You need more education than you can get in a forum. You do not know basic IT standards, terminology and concepts. Do you want to really learn RDBMS or get a kludge?

  4. #4
    Join Date
    Mar 2013
    Posts
    31
    Thanks so much it work for the first attempt . Much appreciated .

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Celko View Post
    Your whole approach to SQL is completely wrong.
    From a mathematical standpoint (especially Relational Algebra) you are technically correct. The problem is that very few people appreciate the benefits of a rigorous approach until they encounter problems which can only be solved using that approach. From the perspective of the OP (Original Poster), their use of SQL makes perfect sense.
    Quote Originally Posted by Celko View Post
    The only data display format in ANSI/ISO Standard SQL is ISO-8601. This “yyyy-mm-dd” and not whatever dialect you are using. Dashes and not slashes.
    You are correct that the ISO-8601 standard is the best format for use with SQL and that it is the ISO approved format for both input and output of dates within SQL, but that doesn't make it correct/useful/desired for every purpose. I don't know why the OP needs a complex key or why it needs to be in this format, but I'd bet that it has to match another system that formats the data that way. We might rail at the pattern, but that doesn't mean that we can change it.
    Quote Originally Posted by Celko View Post
    WHY?? There is nothing wrong with a multi-column key in RDBMS. The goal of any database is to reduce redundancy and you increase it. This is a display issue and should be done in the presentation layer.
    I think that this is more of an ETL issue than a display issue, but that's an assumption... Your opinion is just as good as mine, but neither one affects what the OP needs or wants.
    Quote Originally Posted by Celko View Post
    You need more education than you can get in a forum. You do not know basic IT standards, terminology and concepts. Do you want to really learn RDBMS or get a kludge?
    If you expect the OP to be able to perform at the level that you do, you are entirely correct that they need more education than an internet forum can provide. You're gifted in mathematics, and it took you several decades of classes, experience, and teaching to get where you are now. You're one of the most insightful and gifted SQL authorities that I know, so expecting other people (especially those just getting started) to be even vaguely comparable to your level of expertise is silly.

    This expectation is every bit as unfounded and unreasonable as the ones that you shred when dissecting the problems posted by new users. You really need to re-think your position on this and your expectations... The vast majority of SQL users and every newbie on the planet is going to fall short of this expectation.

    Most posters just want a solution to their problem. Most of the responders here at DBForums go out of our way to try both to help them solve their problem and also educate them a bit in the process. Letting fly with both barrels only makes you look bad, and makes it almost certain that the OP will ignore your response and I see that as a shame.

    It is exceptionally rare that people with your level of expertise will comment on the problems that new users face, and only very slightly more likely that those users will realize how valuable your advice can be. I'd love to see your advice accepted and recognized for its value instead of being criticized for the way that you present it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •