hi,

i have a field that has the date of birth's in the following format,

20/06/1982

i want to take this out of the date of births / , the finish date of birth
field should be like this

20061982

Also

i have a date and time field in sql how can i search one day only the format
is the following

09/03/2005 23:42:33

[quoted text, click to view]

You should store dates in a DATETIME column, not as formatted strings.
Assuming this column is a VARCHAR, first update the value to remove the
quotes:

UPDATE tbl
SET col = RIGHT(col,4)+SUBSTRING(col,4,2)+LEFT(col,2);

Now change it to DATETIME (or SMALLDATETIME):

ALTER TABLE tbl ALTER col DATETIME NOT NULL ;


[quoted text, click to view]

DATETIME columns do not have a "format". A date is just a date. It is your
client application and not SQL Server that controls how the date is
displayed. Assuming that date is 2005-09-03, try:

SELECT *
FROM tbl
WHERE date_col >= '20050903'
AND date_col < '20050904' ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--