March 6th, 2013, 01:48 AM
-
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 ...
March 6th, 2013, 03:53 AM
-
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())
March 6th, 2013, 03:59 AM
-
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 .
March 6th, 2013, 04:11 AM
-
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())
March 6th, 2013, 04:16 AM
-
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.
March 6th, 2013, 04:55 AM
-
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?
March 6th, 2013, 05:03 AM
-
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
March 6th, 2013, 05:36 AM
-
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()))
March 6th, 2013, 10:04 PM
-
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.
March 6th, 2013, 10:27 PM
-
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
March 7th, 2013, 02:10 AM
-
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()))
March 7th, 2013, 02:14 AM
-
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 ....
March 7th, 2013, 04:32 AM
-
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.]
March 7th, 2013, 04:58 AM
-
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?
March 8th, 2013, 12:25 AM
-
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 .