How to manipulate a string field in a SQL statement ?

[TheBionicOrange]

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 ?

[TheBionicOrange]

OK found it.

I used :

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

[Al42]

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

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

[TheBionicOrange]

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.

[Al42]

Then format the grid column.

[si_the_geek]

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.

[Serge]

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

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

[si_the_geek]

I've just seen a ghost!!

Welcome back Serge!


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

[Hack]

I've just seen a ghost!!

Welcome back Serge!

A ghost for sore eyes!

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. Nice to see a Serge post again. Hopefully, the time between this one and the last one won't be as long.

[TheBionicOrange]

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