Solved

Basic SQL

Posted on 2004-10-21
360 Views
Last Modified: 2012-08-14
I have a database field of type numeric 5 containing a number representing a date such as:-
 1040915
 1040915
What kind of date is this and how can I convert to a regular date of MMDDYYYY?
It looks like 1YYMMDD.

Thanks
0
Question by:yassin092898
    7 Comments
     
    LVL 6

    Accepted Solution

    by:
    declare @date varchar(7)

    set @date = '1040915'

    select convert (datetime,substring(@date,2,6),112)

    or just plain

    select convert (datetime,substring'1040915',2,6),112)
    0
     
    LVL 6

    Expert Comment

    by:robertjbarker
    sorry missing (, should be:

    select convert (datetime,substring('1040915',2,6),112)
    0
     
    LVL 14

    Expert Comment

    by:Renante Entera
    Hello yassin!

    Basing from your posted question, for example you have the following records on your table:
    <<< YourTable >>>
    ----------
    Column1
    ----------
    1040915
    1041015
    1041115

    Try running this query :

    Select DateFormat(convert(date,substring(Column1,2,6),12),'mmddyyyy') as NewDate
    From YourTable

    This would be the result :
    ----------
    NewDate
    ----------
    09152004
    10152004
    11152004

    Hope this helps.  Just try it.


    Goodluck!
    eNTRANCE2002 :-)
    0
     

    Author Comment

    by:yassin092898
    thank you both. I am using SQLServer 2000 but

    Select DateFormat(convert(date,substring(Column1,2,6),12),'mmddyyyy') as NewDate

    is giving me this error
    'DateFormat' is not a recognized function name.

    I just want to know if
    entrance2002 is using MS SQLServer 2000.
    thanks
    0
     
    LVL 6

    Expert Comment

    by:robertjbarker
    There is a DateFormat in MS SQLServer 2000, but I don't think it is particularly useful in this situation. And it is not used in a select statement.

    Its use is to set the order of the month, day and year in a date, in a pretty restricted way.  From books on-line an example is:

    SET DATEFORMAT mdy
    GO
    DECLARE @datevar datetime
    SET @datevar = '12/31/98'
    SELECT @datevar
    GO
    0
     
    LVL 7

    Expert Comment

    by:ala_frosty
    try:

    Select convert(date,substring(Column1,2,6),112) as NewDate
    0
     
    LVL 7

    Expert Comment

    by:ala_frosty
    or
    Select convert(date,'20'+substring(Column1,2,6),112) as NewDate
    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    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…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    877 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

    14 Experts available now in Live!

    Get 1:1 Help Now