#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0

    Question conversion error, character to datetime (was "Need help urgently")


    i am working on date functions , here i am converting from varchar to dateandtime and using an alias for that , and using same alias to compare with the current date n time .

    but getting error :The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    this is my query:
    select * from ( select convert(varchar,birthdate, 105)as temp1 from Empdetails ) t where datepart(d,temp1)=datepart(d,GETDATE())
    can any one fine tune this ...
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    the error is, you're converting the birthdate to string, but then in the WHERE clause, forcing it to convert back to datetime to be used in the DATEPART function

    simplify it like this --
    Code:
    SELECT CONVERT(VARCHAR,birthdate,105) AS temp1 
      FROM Empdetails 
     WHERE DATEPART(D,birthdate) = DATEPART(D,GETDATE())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0

    Arrow Need help urgently


    Hi r937,

    The problem i am facing is my birthdate column is of varchar datatype ,not a datetime . so i am converting into from string to datetime .
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by hublikar13
    The problem i am facing is my birthdate column is of varchar datatype ,not a datetime . so i am converting into from string to datetime .
    in that case, your query was wrong, you tried to convert it from string to string

    here ya go --
    Code:
    SELECT *
      FROM ( SELECT CONVERT(DATETIME,birthdate,105) AS temp1 
               FROM Empdetails ) AS t
     WHERE DATEPART(D,temp1) = DATEPART(D,GETDATE())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    in that case, your query was wrong, you tried to convert it from string to string

    here ya go --
    Code:
    SELECT *
      FROM ( SELECT CONVERT(DATETIME,birthdate,105) AS temp1 
               FROM Empdetails ) AS t
     WHERE DATEPART(D,temp1) = DATEPART(D,GETDATE())
    Hi r937,

    Syntax error converting datetime from character string.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by hublikar13
    Syntax error converting datetime from character string.
    in that case, 105 is probably not the right format

    please show a few of the actual values in the birthdate column

    also, would it be possible to change that column so that it's actually a DATE column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0

    Smile Need help urgently


    hi ,

    even i thought the same to change the datatype to datetime , butI dont have permission to change the column to datetime .
    here are some sample values from table :

    26-05-1981
    18-12-1980
    01-07-1979
    07-03-1979
    22-07-1984
    11-01-1977
    24-08-1982
    24-02-1983
    17-04-1983
    13-11-1987
    19-02-1984
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    well, those are definitely format 105, so if you're getting an error, that means that there's at least one value that doesn't match that format

    how about this --
    Code:
    SELECT birthdate
      FROM Empdetails
     WHERE LEFT(birthdate,2) = CONVERT(CHAR(2),DATEPART(D,GETDATE()))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by hublikar13
    hi ,

    even i thought the same to change the datatype to datetime , butI dont have permission to change the column to datetime .
    here are some sample values from table :

    26-05-1981
    18-12-1980
    01-07-1979
    07-03-1979
    22-07-1984
    11-01-1977
    24-08-1982
    24-02-1983
    17-04-1983
    13-11-1987
    19-02-1984
    hi ,

    I am getting empty table.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by hublikar13
    hi ,

    I am getting empty table.
    my be because of different formats of dates , i am facing problem .

    21-4-2011
    20-07-1985
    26-12-1978
    4-8-1983
    7-12-1987
    13-06-1975
    07-05-1981
    2-9-1983
    11-10-1984
    07-03-1975
    21-12-1985
    06-12-1979
    15-02-1981
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by hublikar13
    my be because of different formats of dates
    so you can see how your previous post was misleading, yes? it suggested that the day portion of the date string was always two characters --

    01-07-1979
    07-03-1979

    whereas in fact it can be either one or two

    so we adjust the LEFT function slightly --
    Code:
    SELECT birthdate
      FROM Empdetails
     WHERE LEFT(birthdate,CHARINDEX('-',birthdate)-1) = 
           CONVERT(CHAR(2),DATEPART(D,GETDATE()))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    so you can see how your previous post was misleading, yes? it suggested that the day portion of the date string was always two characters --

    01-07-1979
    07-03-1979

    whereas in fact it can be either one or two

    so we adjust the LEFT function slightly --
    Code:
    SELECT birthdate
      FROM Empdetails
     WHERE LEFT(birthdate,CHARINDEX('-',birthdate)-1) = 
           CONVERT(CHAR(2),DATEPART(D,GETDATE()))
    Hi r937 ,

    Thanks a ton ,, its working ....
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0
    hi r937,

    i'm getting this error while excuting in code.....

    query :[SELECT firstname,midname,lastname,empid,birthdate from empdetails WHERE LEFT(birthdate,CHARINDEX('-',birthdate)-1) = datepart(d,GETDATE()) and Replace(SUBSTRING ( birthdate , 4 , 2),'-','') = datepart(M,GETDATE()) and birthdate is not null and birthdate not like 'null']

    error:[javax.servlet.ServletException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid length parameter passed to the substring function.]
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by hublikar13
    Invalid length parameter passed to the substring function.
    you keep moving the goalposts

    this time, you find a way to fix your problem, okay?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    you keep moving the goalposts

    this time, you find a way to fix your problem, okay?
    hi ,

    I tried all the possibilities but still getting same error .. I am getting the error but value is displaying in query analyzer, but when i am trying to display the same in my front end i am not able to pull the data .

    getting this error :[ javax.servlet.ServletException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid length parameter passed to the substring function ]

    Query:

    SELECT firstname,midname,lastname,empid,birthdate from empdetails WHERE LEFT(birthdate, NULLIF(CHARINDEX('-',birthdate)-1,-1)) = datepart(d,GETDATE()) or LEFT(birthdate, NULLIF(CHARINDEX('-',birthdate)-2,-1)) = datepart(d,GETDATE())and Replace(SUBSTRING ( birthdate , 4 , 2),'-','') = datepart(MM,GETDATE())......

    can u altleast give me a hint .. why this is happening .

IMN logo majestic logo threadwatch logo seochat tools logo