Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    21

    Unanswered: Rearrange String

    I have a database where dates are stored in the following format yyyymmdd. I want to write a query where this is broken up and returned in the format mm-dd-yyyy ... Is this possible in SQL server?

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150

    Re: Rearrange String

    try this

    SELECT CONVERT (datetime,, <DateFIELD> , 110)
    FROM <TABLE>

    Or

    SELECT SUBSTR(<DateFIELD>, 5, 2) + "-" + SUBSTR(<DateFIELD>, 7, 2) + "-" + SUBSTR(<DateFIELD>, 1, 4) AS YOurField
    FROM <TABLE>



    <DateFIELD> - The name of your date field
    Last edited by machado; 02-27-03 at 15:49.

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    if you want mm-dd-yyyy then

    select convert(varchar,getdate(),110)
    select substring('20030201',5,2) + '-' + right('20030201',2) + '-' + left('20030201',4)

    substitute your db field for getdate() or '20030227'
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Feb 2003
    Posts
    21
    Pardon my ignorance, but just for future reference, what is the "110" about?

  5. #5
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    CONVERT (data_type[(length)], expression [, style])

    it is the date style

    0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
    1 or 101 USA mm/dd/yy
    2 or 102 ANSI yy.mm.dd
    3 or 103 British/French dd/mm/yy
    4 or 104 German dd.mm.yy
    5 or 105 Italian dd-mm-yy
    6 or 106 - dd mon yy
    7 or 107 - mon dd, yy
    8 or 108 - hh:mm:ss
    9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
    10 or 110 USA mm-dd-yy
    11 or 111 JAPAN yy/mm/dd
    12 or 112 ISO yymmdd
    -13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
    14 or 114 - hh:mi:ss:mmm(24h)
    20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
    21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)

  6. #6
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Look in bol under convert - it gives all the styles.
    They affect converting to and from character format.

  7. #7
    Join Date
    Feb 2003
    Posts
    21
    Thanks guys... thats been really helpful... I have one other similar problem that ye might be able to help me with... In the same table I have a field for time and it's stored in the following format:

    - 9:20am is stored as 920
    - 3:30pm is stored as 1530
    - 12:20am is stored as 20
    - 12:05 is stored as 5

    Do ye have any suggestion as to how I could call this back in the correct format?

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    try:

    Code:
    if object_id('tempdb..#Tmp') is not null drop table #Tmp
    create table #tmp(dt1 int, tm1 int,dt2 varchar(8), tm2 varchar(4))
    insert into #tmp values(20030228,920,'20030228','920')
    insert into #tmp values(20030228,1530,'20030228','1530')
    insert into #tmp values(20030228,20,'20030228','20')
    insert into #tmp values(20030228,5,'20030228','5')
    
    select * From #tmp
    
    select convert(varchar,cast(cast(dt1 as varchar) as datetime),110)
         , substring(dt2,5,2) + '-' + right(dt2,2) + '-' + left(dt2,4)
      from #tmp
    
    select right(convert(varchar,cast('01-Jan-1753 ' + 
                                left(right('0000' + cast(tm1 as varchar),4),2) + 
                                ':' + 
                                right('0000' + cast(tm1 as varchar),2) as datetime)),7)
         , right(convert(varchar,cast('01-Jan-1753 ' + 
                                left(right('0000' + tm2,4),2) + 
                                ':' + 
                                right('0000' + tm2,2) as datetime)),7)
      from #tmp
    and in anticipation of your next question...

    Code:
    select convert(varchar,cast(cast(dt1 as varchar) + 
                                     ' ' + 
                                     left(right('0000' + cast(tm1 as varchar),4),2) + 
                                     ':' + 
                                     right('0000' + cast(tm1 as varchar),2) as datetime), 100)
         , convert(varchar,cast(substring(dt2,5,2) + '-' + right(dt2,2) + '-' + left(dt2,4) + ' ' +
                                left(right('0000' + cast(tm1 as varchar),4),2) + 
                                ':' + 
                                right('0000' + cast(tm1 as varchar),2) as datetime), 100)
      from #tmp
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Feb 2003
    Posts
    21
    Is there any easier way of doing this.... this way seems very complicated !

Posting Permissions

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