Trouble converting text to a datetime

[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

This is my current query and 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

 

[zhavic]

Use CONVERT function and '+' sign instead of '&'

select * from SVC00200
where SVC00200.SRVSTAT = '10A'
AND SVC00200.SRVTYPE = 'SVC'
AND CONVERT( datetime, left(SVC00200.USERDEF2,10) + ' ' + RIGHT(SVC00200.USERDEF2,8), 103) < getdate()

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Stupot2k]

Thanks for your help, Zhavic, I've tried that and I get the following error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

It looks like datetime expects a particular format: my string has been formatted to look like this:

dd/mm/yy_hh:mm:ss

Datetime appears to want dd mon yyyy hh:mm:ss:mmm with style 103

Do you think that is my problem, am I ion the right track.

I am trying to use MID to reformat the string differently, SQL doesn't seem to like MID. What can you use instead.

 

[ArtieChoke]

Check out substring

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[SQLBill]

The big issue is the underscore between the date and time. SQL Server understands a lot of different date/time formats. You could even be explicit in your script and add SET DATEFORMAT DMY at the beginning. But that won't help with the underscore.

This will replace the underscore with a space:

SELECT REPLACE(SVC00200.USERDEF2, '_', ' ')

this puts the whole thing together:

SET DATEFORMAT DMY
select * from SVC00200
where SVC00200.SRVSTAT = '10A'
AND SVC00200.SRVTYPE = 'SVC'
AND CONVERT(datetime, left((REPLACE(SVC00200.USERDEF2, '_', ' '),10) + ' ' + RIGHT((REPLACE(SVC00200.USERDEF2, '_', ' '),8), 103) < getdate()

DISCLAIMER: I haven't run the script, but it should work unless I missed a parenthesis or have a typo.

-SQLBill