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

    Unanswered: Convert numeric date field into date field

    I am using SQL Server 2014 I want to create a view. I have a field call inv_date and is numeric, a date field yymmdd what I want to do is convert it into mm/dd/yyyy and call the field Invoice_Date. I try to google in how to convert, but everything is for yyyymmdd format not yymmdd. All I want to do is to type in my view a date for example: Between 1/1/2016 and 6/24/2016. Any tip will be appreciated. Thank you in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That particular dance requires a two-step.
    Code:
    DECLARE @i INT = 160220
    
    SELECT @i, Convert(DATE, Convert(CHAR, @i), 12)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2013
    Posts
    30
    Thanks Pat

  4. #4
    Join Date
    Jan 2013
    Posts
    353
    Provided Answers: 1

    Your mindset is wrong.

    I am using SQL Server 2014 I want to create a view. I have a field call inv_date and is numeric, a date field yymmdd what I want to do is convert it into mm/dd/yyyy and call the field Invoice_Date. I try to google in how to convert, but everything is for yyyymmdd format not yymmdd. All I want to do is to type in my view a date for example: Between 1/1/2016 and 6/24/2016. Any tip will be appreciated. Thank you in advance.
    Your whole approach to this is wrong. First of all, a field in SQL is part of a temporal value (year, month, day, hour, minute, second); you probably meant to say column. The term field also refers to part of a record in filesystems; rows are not records.

    Secondly, we have a DATE data type in SQL and have for years. This is an internal binary format that each SQL product can define anyway it wishes as long as it works. The ANSI/ISO standard defined a display format based on ISO 8601 (yyyy-mm-dd) and this is the only format allowed in the standard language. Microsoft allows you to drop the dashes, that is pure dialect.

    You might not know what that what you are doing is COBOL! That language keeps dates as strings. For example, in COBOL numerics are stored in the fields of records as strings of digits (well, mostly; there are some exceptions, do not worry about it) and not as a binary format like SQL (actually SQL is not obligated to store data in any particular physical format, but almost every product keeps integers as binary formatted data). Your level of abstraction has not gone up in high enough.

    CREATE TABLE Foobar
    (..
    invoice_date DATE NOT NULL,
    ..
    );

    My advice is that you download a copy of "Temporal Queries in SQL" by Rick Snodgrass; it is a free PDF from the University of Arizona

  5. #5
    Join Date
    Mar 2013
    Posts
    30
    Celko,

    Your right I meant column instead of field. I am just creating a report from a AS/400 table downloaded into SQL (not my preference). I will look into Temporal Queries. Thank you.

Posting Permissions

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