SharePoint 2010 CAML - Casting Date Fields?

Question

So I'm trying to build a CAML Query in SharePoint to query a list where Date is greater than a certain value say 1/1/2016, my CAML looks kinda like this

<Where>
<Geq>
<FieldRef name = 'BillingDate'/>
<value type='DateTime'>1/1/2016</value>
</Geq>
</Where>

When I do this with the list, I get weird results. Upon looking at this list I found out that the BillingDate field is actually a text field and not a date. I'm kinda loathe to change it to a Date Field since this is a long standing app with lots of business logic that might be reliant on Billing Date being a text field. I'm using PowerShell for querying.

Is there any way offhand to cast a text field as a Date for CAML purposes? I'm fairly certain all the date values would cast just fine (didn't see anything invalid offhand). Another thought I had was maybe creating a new date field "BillingDate_2" and migrating the data from one to another. Then maybe I can query. However if anyone's ever managed to cast a SharePoint field as something else in query it may be the simpler solution here.

Answer 1

No you cannot type cast a text field as date in caml query. You can create BillingDate_2 column and using powershell you can convert the BillingDate values to date Time and fill it into BillngDate_2 and then you can query on the BillingDate_2, but any new items added will not have BillingDate_2 values.

Another approach is to fetch all the values from the list and then for loop all the items, in that you can have a if condition to check whether BillingDate (you can type convert to date) is greater than your value and do your operation.

To know more about converting string to Date time, refer this link,

https://stackoverflow.com/questions/38717490/convert-a-string-to-datetime-in-powershell