Results 1 to 10 of 10

Thread: Datatype

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Datatype

    I am trying to convert a NVCHAR datatype to a DATETIME data type is that possible???

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Only if it's a valid date. Post a simple example of failure along with associated code and data values and let's see what we can do.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    DECLARE @x TABLE (Col1 nvarchar(25))
    INSERT INTO @x(Col1) 
    SELECT '12/31/2004 11:59:59' UNION ALL
    SELECT 0 UNION ALL
    SELECT 'Happy New Year'
    
    SELECT CONVERT(datetime,Col1) FROM @x WHERE ISDATE(Col1) = 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.

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Rev table

    Here is the select statement I am using but when I use it it doesnt just give me the 2004 only it gives me everything in the table
    Code:
    SELECT   TM#, LastName, FirstName, [Date]
    FROM   Revocations_Tbl
    WHERE  ([Date] BETWEEN '01/01/2004' AND '12/31/2004')
    this is what the tables design looks like all the other tables have DATETIME for their datatypes and I have no problems, when I tried to convert the datatype I got alot of error messages
    Last edited by desireemm; 04-15-09 at 17:23.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    To be honest...I'd fix the table...

    ALSO! Anyone figure out why I'm getting the conversion error in the following...

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myRevocations_Tbl99(TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))
    GO
    
    INSERT INTO myRevocations_Tbl99 (TM#, LastName, FirstName, [Date])
    SELECT 1, 'Kaiser', 'Brett', '10/24/2004'    UNION ALL
    SELECT 2, 'Kaiser', 'Brett', '01/01/2005'    UNION ALL
    SELECT 3, 'Kaiser', 'Brett', '12/31/2003'    UNION ALL
    SELECT 4, 'Desiree', 'A', '01/01/2004'       UNION ALL
    SELECT 5, 'Desiree', 'Whos', '10/19/2004'    UNION ALL
    SELECT 6, 'Desiree', 'Your', '10/19/200a'    UNION ALL
    SELECT 7, 'Desiree', 'Their', '12/31/2004'   UNION ALL
    SELECT 8, 'A Row', 'Of Data 1', 'aaaaaaaaaa' UNION ALL
    SELECT 9, 'A Row', 'Of Data 2', '0'	     UNION ALL
    SELECT 0, 'A Row', 'Of Data 3', '123456789'
    GO
    
    -- Your's
    
    DECLARE @x varchar(10), @y varchar(10)
    SELECT @x = '01/01/2004', @y = '12/31/2004'
    SELECT   TM#, LastName, FirstName, [Date]
    FROM   myRevocations_Tbl99
    WHERE  [Date] BETWEEN @x AND @y
    GO
    
    -- Mine
    
    DECLARE @x datetime, @y datetime
    SELECT @x = '01/01/2004', @y = '12/31/2004'
    SELECT   TM#, LastName, FirstName, [Date]
    FROM   (SELECT TM#, LastName, FirstName, [Date]
    	  FROM myRevocations_Tbl99 
    	 WHERE ISDATE([Date])=1) AS XXX
    WHERE  DATEDIFF(yy,CONVERT(datetime,[Date]),@x) = 0
      AND  DATEDIFF(yy,CONVERT(datetime,[Date]),@y) = 0
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myRevocations_Tbl99
    GO
    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 2004
    Posts
    8
    Quote Originally Posted by Brett Kaiser
    ALSO! Anyone figure out why I'm getting the conversion error in the following...
    It looks like the optimizer is flattening out your query into a single table scan, eliminating the ISDATE, then choking on the strings that aren't dates. At least that's what the estimated plan indicates.
    Seems like an odd thing to do.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Put the row for [TM#] 8 first, then it will work.

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    Put the row for [TM#] 8 first, then it will work.

    -PatP

    Like I'd have any control over the data....
    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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well actually Pat, It blows up right away....
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This works...

    Code:
    DECLARE @xxx table (TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))
    
    INSERT INTO @xxx (TM#, LastName, FirstName, [Date])
    SELECT TM#, LastName, FirstName, [Date]
    	  FROM myRevocations_Tbl99 
    	 WHERE ISDATE([Date])=1
    
    DECLARE @x datetime, @y datetime
    SELECT @x = '01/01/2004', @y = '12/31/2004'
    SELECT   TM#, LastName, FirstName, [Date]
    FROM   @xxx
    WHERE  DATEDIFF(yy,CONVERT(datetime,[Date]),@x) = 0
      AND  DATEDIFF(yy,CONVERT(datetime,[Date]),@y) = 0
    GO
    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.

Posting Permissions

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