The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
VBScript Date()
Posted: 16 Jan 09 8:49 AM
|
I am trying to change some code to use the date() function, but I am not getting the results I want.
strPrice = GetField("CONTRACTPRICE", "C_CONTRACTPRICING", "PRODUCTID = '" & sProductID & "' and AccountID='" & application.BasicFunctions.CurrentAccountID & "' AND Date() > EFFECTIVEDATE and Date() <= EXPIRATIONDATE" )
When I manually run the equivalent query in QA, I get the desired results, but with this function, it seems to not check the current date value properly. Does anyone have any thoughts or suggestions? |
|
|
|
Re: VBScript Date()
Posted: 16 Jan 09 1:35 PM
|
It looks like you are making the SQL say literally
"... AND Date() > EFFECTIVEDATE ..."
I think you want something like this:
"... AND '" & Date() & "' > EFFECTIVEDATE ..."
|
|
|
|
Re: VBScript Date()
Posted: 16 Jan 09 2:05 PM
|
Or you could to use GETDATE() or GETUTCDATE() SQL functions.
"... AND GETDate() > EFFECTIVEDATE ..." Or "... AND GETUTCDate() > EFFECTIVEDATE ..."
My experience is that they will get through the parser, but test.
|
|
|
|
Re: VBScript Date()
Posted: 16 Jan 09 2:11 PM
|
| I have tried both, but with the same results. I would assume this may have something to do with how Date() is formatted compared to the MS SQL formatting of date, YYYY-MM-DD HH:MM:SS. |
|
|
|
Re: VBScript Date()
Posted: 16 Jan 09 2:23 PM
|
Run the SLX Profiler and capture the SQL statement hitting the DB. (and/or check the Error tab to see if it failed to parse/execute).
|
|
|
|
Re: VBScript Date()
Posted: 21 Jan 09 10:28 AM
|
You got it. If you pass the date formatted as you stated above, you won't have any issues. Otherwise the date is formatted based on the regional settings of the pc. That may not be a valid date format for sql server. What's worse is that it may work on Jan 1st but not on the 31st.
I've used the following generic routines in the past.
Public Function GetIsoDateString(dteDate)
Dim strRetVal
Dim strYear, strMonth, strDay
strYear = CStr(year(dteDate))
strMonth = CStr(month(dteDate))
If Len(strMonth) = 1 Then strMonth = "0" & strMonth
strDay = CStr(day(dteDate))
If Len(strDay) = 1 Then strDay = "0" & strDay
strRetVal = strYear & "-" & strMonth & "-" & strDay
GetIsoDateString = strRetVal
End Function
Public Function GetIsoDateTimeString(ByVal dteDate)
Dim strRetVal
Dim strHour, strMin, strSec
strRetVal = GetIsoDateString(dteDate)
strHour = CStr(Hour(dteDate))
strHour = String(2 - Len(strHour), "0") & strHour
strMin = CStr(Minute(dteDate))
strMin = String(2 - Len(strMin), "0") & strMin
strSec = CStr(Second(dteDate))
strSec = String(2 - Len(strSec), "0") & strSec
strRetVal = strRetVal & " " & strHour & ":" & strMin & ":" & strSec
GetIsoDateTimeString = strRetVal
End Function
|
|
|
|