1

I need help adjusting the query below to work under ms sql. I migrating Access Databases to SQL Server and the query below is falling.

I am getting the following error:

"Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]'TimeValue' is not a recognized built-in function name."

Thank you in advance.

SELECT  M.MessageID, M.Subject, M.ShortDesc, M.subName, M.Submitter, C.CategoryName,
        DateValue( 
            IIF ( 
                DatePart( 'w', IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow)) ) = 1, 
                DateAdd( 'd', 1, IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow)) ), 
                IIF(
                    DatePart( 'w', IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow)) ) = 7, 
                    DateAdd( 'd', 2, IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow)) ),
                    IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow))
                ),
            )
        ) AS DateToShow
FROM    MessageInfo M, Categories C
WHERE   M.Approved = true 
    AND C.CategoryID = M.Category 
            ORDER BY C.CategoryPreference, C.CategoryName, 
    DateValue( 
        IIF ( 
            DatePart( 'w', IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow)) ) = 1, 
            DateAdd( 'd', 1, IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow)) ), 
            IIF(
                DatePart( 'w', IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow)) ) = 7, 
                DateAdd( 'd', 2, IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow)) ),
                IIF(TimeValue(M.DateToShow)<='10:00',M.DateToShow,DateAdd('d',1,M.DateToShow))
            ),
        )
    )
    DESC, M.Subject
Jorge Ben
  • 51
  • 7
  • Not sure what TimeValue is? Is that an Access function? If so, what does it return? I would recommend not using those datepart shortcuts when using DatePart and DateAdd. It is a preference thing but putting the actual name there is a lot clearer. – Sean Lange Jul 15 '15 at 19:40

1 Answers1

2

The TimeValue function in VBA returns the time portion of a date string as a date variant.

For MSSQL 2008 and later you can cast to a time by using:

CAST(M.DateToShow AS time)

instead of 

TimeValue(M.DateToShow)

References:

Community
  • 1
  • 1
Don Jewett
  • 1,867
  • 14
  • 27
  • if change "TimeValue(M.DateToShow)" to "CAST(M.DateToShow AS time)" I get the following error: [SQLServer JDBC Driver][SQLServer]Invalid parameter 1 specified for dateadd. – Jorge Ben Jul 15 '15 at 20:13
  • You could try CONVERT(VARCHAR(8),M.DateToShow),108). You have to use the same time returned from CreateODBCTime() so the types being compared match – Don Jewett Jul 15 '15 at 20:19
  • when add "CONVERT(VARCHAR(8),M.DateToShow),108)" I get the following error: [SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'CONVERT'. – Jorge Ben Jul 15 '15 at 21:11
  • oops. remove extra parenthesis after M.DateToShow – Don Jewett Jul 15 '15 at 21:13
  • I updated the query. It work on access and not sql. Still getting incorrect syntax near CONVERT. – Jorge Ben Jul 16 '15 at 18:02
  • @JorgeBen - First, what version of SQL Server are you using? Second, that is a really big query. Start small. Create a trivial example that tests only the CONVERT code. If it works, add in the next piece. If not, you have an idea what broke it - and a compact, *runnable* [repro case](http://www.sscce.org/) others can try independently of your environment :) Side note, VBA functions like TimeValue will not work with JDBC. – Leigh Jul 16 '15 at 21:53