Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Complex Date Formatting

    I hope I explain myself clear enough. I have an integer field of date values: 20031231. Some of the values in the field are zero. I want to convert the integer to 12/31/2003. Right now I am doing it with 2 views. The first view takes the zeros and converts them to null by using case. The second view uses convert to make it into the date string I want. Is there some way I can do it all in one view?
    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select cast(cast(nullif(@IntDate, 0) as char(8)) as datetime)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ..or more robust (will return null without error if integer is not a valid date):

    select cast(cast(nullif(@IntDate*isdate(cast(nullif(@IntD ate, 0) as char(8))), 0) as char(8)) as datetime)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What do you mean by 2 views?

    Can you post the code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Posts
    328
    I have one view that uses case to find the zeros and replace them with nulls. Then I have another view that uses convert to convert the not null values to the date string I want. I can't use the convert on the first view because it won't work on an integer string. In the second view I convert the non nulls to a char string and then to the date format I want. In other words I am using view on a view to do this. Thanks.
    Last edited by exdter; 01-20-04 at 14:00.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well....how about something like this...


    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT 0 	 UNION ALL
    SELECT 20031231  UNION ALL
    SELECT 20010101  UNION ALL
    SELECT 19601024  UNION ALL
    SELECT 11111111
    GO
    
    SELECT * FROM myTable99
    GO
    
    ALTER TABLE myTable99 ADD DateComputed AS 
    CASE WHEN ISDATE(SUBSTRING(CONVERT(varchar(15),Col2),5,2)+'/'+SUBSTRING(CONVERT(varchar(15),Col2),7,2)+'/'+SUBSTRING(CONVERT(varchar(15),Col2),1,4))=1
         THEN CONVERT(varchar(25),SUBSTRING(CONVERT(varchar(15),Col2),5,2)+'/'+SUBSTRING(CONVERT(varchar(15),Col2),7,2)+'/'+SUBSTRING(CONVERT(varchar(15),Col2),1,4),120)
         ELSE NULL
    END
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    But why not fix the datatype in the first place?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2003
    Posts
    328
    Thanks for your reply. What do you mean by fixing the datatype in the first place? You mean to alter the table and change it from integer to char?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by exdter
    Thanks for your reply. What do you mean by fixing the datatype in the first place? You mean to alter the table and change it from integer to char?
    Integer to datetime....

    But first you'll have to cleanup the mess.....

    Did you like the computed column solution?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2003
    Posts
    328
    I can't alter the table because it is the backend to software we bought. They will not give support if we alter the tables. I have to work out the code you sent. I am not as advanced as you are (is anyone?) and need to sort out what it does and how to implement it. I thought about using the concatination but just wondered if there was a way to use char, case, and convert in the same line. I see your code alters the original table, which I can't do.
    Thanks for your time.
    Last edited by exdter; 01-20-04 at 14:28.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, just take the CASE Statement out of the ALTER

    Should work as well in your view...

    So you got a 3rd party product that stores dates as an int....

    I love these guys...can you say who it is?

    And if you need help with the view...post it....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Aug 2003
    Posts
    328
    They are called SoftPro. http://www.softprocorp.com
    Thanks.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hello! Anybody there? Did you TRY select cast(cast(nullif(@IntDate, 0) as char(8)) as datetime)????
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Aug 2003
    Posts
    328
    Yes I tried it and when I try to open the view, my system locks up and I have to close the QA tool.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hello?

    Code:
    DECLARE @IntDate int
    SET @IntDate = 20031231
    select cast(cast(nullif(@IntDate, 0) as char(8)) as datetime)
    SET @IntDate = 0
    select cast(cast(nullif(@IntDate, 0) as char(8)) as datetime)
    SET @IntDate = 11111111
    select cast(cast(nullif(@IntDate, 0) as char(8)) as datetime)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Aug 2003
    Posts
    328
    I can use this in a view?

Posting Permissions

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