How to manipulate a string field in a SQL statement ?

I am grabbing a field from a table that is defined as a string. It is actually a date in the format YYYYMMDD.
What I would like to do is put something in the SQL statement so that when it comes back it reads as "DD/MM/YYYY".

Is this easy enuff to do ?

OK found it.

I used :

VB Code:

SELECT     SUBSTRING(Audit_Date, 7, 2) + '/' + SUBSTRING(Audit_Date, 5, 2) + '/' + SUBSTRING(Audit_Date, 1, 4) AS Expr1
FROM         Audit_Location_Summary

:)

You could just format the date field value once you get it:

MyStr = Format(MyRS.Fields("Audit_Date").Value,"dd/mm/yyyy")

No I couldn't, well not in this case. The recordset is being thrown straight at a grid. Thats why I needed to get it sorted within the SQl.

Then format the grid column.

As long as you are using a "proper" database (like SQL Server, as I think TheBionicOrange is) it's much more efficient to do it in the SQL. ;)

if you are using SQL Server then its a lot simpler. You can just construct your sql statement to convert your string into a date :)

Code:

---

select convert(varchar(10), convert(datetime, YourStringDateField), 103) from YourTable.....

---

the general syntax is
convert(datatype, FieldToConvert, FormatOptions)

In our case formatOption 103 will give you string in a date-like format dd/mm/yyyy

:eek: I've just seen a ghost!!

Welcome back Serge! :wave:


You are indeed correct, I should have thought of that. :ehh:

Quote:

---

Originally Posted by si_the_geek

:eek: I've just seen a ghost!!

Welcome back Serge! :wave:

---

A ghost for sore eyes! :wave:

I had to double check the date time stamp on the post. I thought for a moment someone had posted in the very old thread. :eek: Nice to see a Serge post again. Hopefully, the time between this one and the last one won't be as long. http://www.vbforums.com/attachment.p...id=38370&stc=1

Thanks for that Serge .. I'll make a note of that one.