Convert Text to Date in a Query

[Stupot2k]

I'm new to SQL so be gentle with me.

We have a system called Microsoft Great Plains, it uses Business Alerts which as SQL Query based. In order to write effective queries I run them in query analyzer and then paste them into MGP. Our vendor has carried out some bespoke work for us which has resulted in an SLA on a form which holds date and time information, unfortunately it is a text UDF. The data is "dd/mm/yyyyhh:mm:ss" you will notice there is no space. I need to be able to change the text to a date/time variable to use DATEDIFF in my query, can anybody help? I have been able to change the string around to add a space etc, but cannot do the last stretch,

Please help

Stu

 

[PruSQLer]

Well, this works in DB2:

select date(substr('31/08/1951:08:31:51',4,2) || '-' || substr('31/08/1951:08:31:51',1,2) || '-'
|| substr('31/08/1951:08:31:51',7,4) )
from table1

DB2 didn't like the DD/MM/YYYY format so I had to rearrange the string using substring and concatenation. HTH

 

[Stupot2k]

Thanks I tried it but Date is not recognised in my query analyzer.

This is the query I am using and the response.

QUERY
select * from SVC00200
where SVC00200.SRVSTAT = '10A'
AND SVC00200.SRVTYPE = 'SVC'
AND date(left(SVC00200.USERDEF2,10) & ' ' & RIGHT SVC00200.USERDEF2,8)) < getdate()

RESPONSE
Server: Msg 195, Level 15, State 10, Line 4
'date' is not a recognized function name.

Stu

 

[JarlH]

I suggest you try a MS forum. MS's SQL isn't ANSI compliant when it comes to DATE handling.

 

[Stupot2k]

I'll do that thanks.

Stu