Solved

Basic SQL - Date

Posted on 2004-10-26
1,190 Views
Last Modified: 2012-05-05
I have a numeric field containing a date formated as CYYMMDD(C = Century, if C=1 it is 2000, if C=0 it is 1900). Some dates are :-
1040915
1040915
1040915
1040917
1040917
1040428

I want t display it as MM-DD-YYYY.
 This works for me but I feel the formating is too long.

select CONVERT(CHAR(10), CONVERT(DATETIME, SUBSTRING(LTRIM(STR(A.DateCreated)),2,6), 112), 110) 'Date Created'
from Order A

some result
09-15-2004
09-15-2004
09-15-2004
09-15-2004
09-15-2004
04-28-2004
08-18-2004
08-18-2004
08-18-2004
09-15-2004
09-15-2004


If you have a better Approach let me. If you think I am doing the right thing let me know too. I will award the points either way.

Thanks
0
Question by:yassin092898
    4 Comments
     
    LVL 8

    Accepted Solution

    by:
    Forget the formatting.

    The issue is performance.

    If you are happy with the performance and the code works stick with it.  Do not over engineer a solution as you may be taking time away from other important issues.

    If it works (performance issues not withstanding) do not fix it.

    Cheers Sash
    0
     
    LVL 26

    Assisted Solution

    by:Hilaire
    Personnally i'd create a new computed column on the table, in true datetime format

    alter table Order add DateTime_Created as cast(cast(19000000 + @day as varchar) as datetime)

    then i'd use this column to make all sorts of things that can't be achieved with your above format :

    - retrieve records that are less than 10 days old
    select * from Order where datediff(day, DateTime_Created, getdate()) < 10

    - calculate a difference in days between two records
    - retrieve week numbern, month name, ....
    date functions are quite powerful in SQL Server, it's really a shame not to use datetime format

    As for the formatting, it should be handled on the front-end side.

    HTH

    Hilaire

    0
     
    LVL 8

    Expert Comment

    by:SashP
    Your function does not work when the century = 0.

    So "if it aint broke don't fix it" does not seem to apply here.

    Have a look at what happens when you get dates in the 1900s


    declare @a table (adate int)

    insert into @a values('1040915')
    insert into @a values('1990915')
    insert into @a values('1040915')
    insert into @a values('1040917')
    insert into @a values('0000917')
    insert into @a values('0990421')

    select * from @a

    select STR(adate) from @a

    0
     
    LVL 8

    Expert Comment

    by:SashP
    Then look at

    declare @a table (adate int)

    insert into @a values('1040915')
    insert into @a values('1990915')
    insert into @a values('1040915')
    insert into @a values('1040917')
    insert into @a values('0000917')
    insert into @a values('0990421')

    select * from @a

    select case when adate > 999999 then '20' else '19' end +  right('000' + ltrim(STR(adate)),6) from @a
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    If you are an Active Directory administrator working with AD data in SQL Server, then this article is for you! INTRODUCTION As AD admins or those having to deal with AD data, you probably have had to convert a timestamp or two like last logo…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how the fundamental information of how to create a table.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now