Problem in displaying and using date as criteria

[I V]

Hello,
I have nvarchar field in SQL server 2000 database like yyyymmdd

I wanted to change it to datetime to use it in my criteria and separated to use it in three TextBoxes, one for dd, another one for mm and the last one for yyyy.

I used the convert function as below

SQL1 = SQL1 + " where convert(datetime,EMP_SENIORITY_DT_G) = '" + TextBox2.Text + "' ";

First I can't separate the date after conversion to three pieces dd/mm/yyyy.

second when I display the result of the sql it must be appear like 30/12/1964 , but instead of that it appeared like that yyyymmdd for example 19641230

Hope you can help me,
Thank you

[Plater]

Hello,
I have nvarchar field in SQL server 2000 database like yyyymmdd

I wanted to change it to datetime to use it in my criteria and separated to use it in three TextBoxes, one for dd, another one for mm and the last one for yyyy.

I used the convert function as below

SQL1 = SQL1 + " where convert(datetime,EMP_SENIORITY_DT_G) = '" + TextBox2.Text + "' ";

First I can't separate the date after conversion to three pieces dd/mm/yyyy.

second when I display the result of the sql it must be appear like 30/12/1964 , but instead of that it appeared like that yyyymmdd for example 19641230

Hope you can help me,
Thank you

You have a nvarchar field with numbers in it like:
19641230
20070522

and you want to create a query where the result would be 30/12/1964 and 22/05/2007 ?

SELECT
( SUBSTR(myfieldname,6,7) + '/' + SUBSTR(myfieldname,4,5) +SUBSTR(myfieldname,0,3) ) AS [DATE TIME]
 

My substr numbers may be off, but you should be able to sperate them out like that.

[I V]

Thank you Plater, but I got this error

'SUBSTR' is not a recognized built-in function name !!

[I V]

Hi Plater

SUBSTRING worked great, thank you again