Convert text to date

[Brianfree]

Hi, i have a table with a text field with the following information in...
Start End
200404 200903

I want to convert these to a dates in my query like 01/04/2004
(dd/mm/yyyy) so i can do a seach for data between dates?

Does anyone know how to do this?

Regards,

BF

 

[Geoka]

if the field in your table is datetime , then there is no need to convert to dd/mm/yyyy format. You can directly use '20040401' in your filter condition.

 

[Brianfree]

Thanks for replying - will give this a try.

Many thanks,

Brian

 

[Brianfree]

Hi, i have tried converting the data type tp date/time and saving but this just deleted all the data... so i reimported the data but this time as datetime but it didn't import anything. Looks like its because its missing two chars.. Could add '01' to the end of each row??? not sure what to do...

any other ideas?

Many thanks,

BF

 

[bborissov]

Yes, you should if 200404 is YYYYMM format.
What you tried so far?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RyanEK]

Hi,

How about...

declare @temp table (date varchar(6))
insert into @temp (date) values ('200903')
insert into @temp (date) values ('200904')
insert into @temp (date) values ('200905')

select * from @temp
where convert(datetime,date+'01',112) between '2009-03-15' and '2009-04-15'

Ryan