Date Problem 3

[charbrad]

I have a column within a table that is pulling the date, folder and file name as:

#2007\12\12\15\DOCFILE1.TXT

Is there a way to convert this in SQL to get just the date?

[b]2007\12\12[/b]

 

[bborissov]

SELECT CONVERT(Datetime, REPLACE(SUBSTRING(YourField,2,8),'/',''))
FROM YourTable

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

Use the [!]SubString[/!] function

[tt][blue]Select SubString(ColumnName, 2, 10) As TheDate[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Oops, I used the wrong slash

SELECT CONVERT(Datetime, REPLACE(SUBSTRING(YourField,2,8),'\',''))
FROM YourTable

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

8 ?

Are you sure?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

How about another flavor?

Select Right(Left(YourField, 11),10)
      From YourTable

 

[bborissov]

No!!!!!
I am not
I don't know why my math is not working today :
4+2+2+2 = 8 ???????????????????????
Thank God I don't count money I should get that way
If I should give, that is another question

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[charbrad]

I used 10 in place of 8....worked like a charm. Have a star!

 

[gmmastros]

Boris,

You are too cool. Keep up the good work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

Boris,

There's nothing wrong with 8!

SELECT CONVERT(Datetime, SUBSTRING(REPLACE(YourField,'\',''),2,8))
FROM YourTable

 

[gmmastros]

Timing is everything.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom