Results 1 to 9 of 9

Thread: Issue On Views

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Issue On Views

    I have a view which has a date field. However, this date field is in the form of a number: 20041401. There are also places where the date is 0 (zero). I want to get this date to look like 1/14/04. I did this by creating a view on the first view, and used case to set the 0 (zero) values to null, and then I created another view on the last one to convert the not null values to a date using cast. Is there a simpler way to do what I want? Pretty soon I will lose track of all my views.
    Thanks.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Issue On Views

    Maybe you should cast or convert the datetime column the right way in your first view.

  3. #3
    Join Date
    Aug 2003
    Posts
    328

    Re: Issue On Views

    I can't because the date string is in the form of a number. When I execute the view, and it comes across a 0 (zero) I get an error. Because cast can't convert a number to char.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not sure I follow..but...

    Code:
    DECLARE @x int
    SELECT @x = 20041401
    SELECT CONVERT(varchar(10),
    	CONVERT(datetime,SUBSTRING(CONVERT(char(8),@x),7,2) 
    		+ '/' + SUBSTRING(CONVERT(char(8),@x),5,2)
    		+ '/' + SUBSTRING(CONVERT(char(8),@x),1,4)),1)
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Issue On Views

    Originally posted by exdter
    I can't because the date string is in the form of a number. When I execute the view, and it comes across a 0 (zero) I get an error. Because cast can't convert a number to char.
    Really?

    SELECT CONVERT(varchar(10),0)
    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.

  6. #6
    Join Date
    Aug 2003
    Posts
    328

    Re: Issue On Views

    That's what I need. Thanks.

  7. #7
    Join Date
    Oct 2003
    Posts
    706

    Thumbs up

    I strongly agree with the notion that, if you're dealing with date-type information that hasn't been stored as a Date field (and which, e.g. for application compatibility reasons, can't be converted now), it is highly desirable that you should use a base view that Casts the value to a date; then base other views on that.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And doen't that make it an non-updateable view?
    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
    Thanks.

Posting Permissions

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