[hublikar13]

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 ...

[r937]

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 --

SELECT CONVERT(VARCHAR,birthdate,105) AS temp1 
  FROM Empdetails 
 WHERE DATEPART(D,birthdate) = DATEPART(D,GETDATE())

[hublikar13]

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 .

[r937]

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 --

SELECT *
  FROM ( SELECT CONVERT(DATETIME,birthdate,105) AS temp1 
           FROM Empdetails ) AS t
 WHERE DATEPART(D,temp1) = DATEPART(D,GETDATE())

[hublikar13]

Originally Posted by r937

in that case, your query was wrong, you tried to convert it from string to string

here ya go --

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.

[r937]

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?

[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

[r937]

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 --

SELECT birthdate
  FROM Empdetails
 WHERE LEFT(birthdate,2) = CONVERT(CHAR(2),DATEPART(D,GETDATE()))

[hublikar13]

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.

[hublikar13]

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

[r937]

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 --

SELECT birthdate
  FROM Empdetails
 WHERE LEFT(birthdate,CHARINDEX('-',birthdate)-1) = 
       CONVERT(CHAR(2),DATEPART(D,GETDATE()))

[hublikar13]

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 --

SELECT birthdate
  FROM Empdetails
 WHERE LEFT(birthdate,CHARINDEX('-',birthdate)-1) = 
       CONVERT(CHAR(2),DATEPART(D,GETDATE()))

Hi r937 ,

Thanks a ton ,, its working ....

[hublikar13]

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.]

[r937]

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?

[hublikar13]

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 .