6/19/2025 4:29:52 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Date Formatting for Grid Queries w/UTC Support (Again)
Posted: 02 Aug 07 10:47 AM
|
I've been able to format date ranges for grid query SQL statements which take the UTC format into consideration. That format is: CCYYMMDD hh:mm:ss. But to do so, my logic currently depends on having workstations set their "Short Date" form to: MM/DD/YYYY. Unfortunately, I can't always depend on that setting, and many machines are defaulted to the M/D/YYYY setting. Thus, is there a better method than the one I'm now using.
For reference, I set up the dates as follows:
sStartDate = DateSerial(vYear, vMonth, vPriorDay)
sEndDate = DateSerial(vYear, vMonth, vPriorDay + 1)
lblDisplayDate.Caption = sStartDate
'** Date Format must be as per UTC Support [CCYYMMDD hh:mm:ss] (see LAN Dev. Ref v7.0 on p. 165)
sStartDate = Mid(sStartDate,7,4) & Mid(sStartDate,1,2) & Mid(sStartDate,4,2) & sTimed
sEndDate = Mid(sEndDate,7,4) & Mid(sEndDate,1,2) & Mid(sEndDate,4,2) & sTimed
sWQL = sStartDate & "') AND (A1.STARTDATE < '" & sEndDate
sQL = sPQL & sWQL & sZQL
Note: sPQL and sZQL are pre-setup SQL prefixes and suffixes clauses and sTimed = " 00:00:00"
 |
|
|
|
Re: Date Formatting for Grid Queries w/UTC Support (Again)
Posted: 02 Aug 07 11:24 AM
|
Use parameters so you no longer have to worry about formatting a string. This is such a pain 
sHistoryCountSQL = " SELECT " _
& vbCrLf & " Count(H.HistoryID) " _
& vbCrLf & " FROM " _
& vbCrLf & " History H " _
& vbCrLf & " WHERE " _
& vbCrLf & " H.Type = ? " _
& vbCrLf & " AND H.CompletedDate >= ? " _
& vbCrLf & " AND H.CompletedDate < ? " _
& vbCrLf & sUserCondition
' Excluded Code for creating ado objects
With Command.Parameters
Call .Append(Command.CreateParameter("Type", adInteger, adParamInput))
Call .Append(Command.CreateParameter("Begin", adDate, adParamInput))
Call .Append(Command.CreateParameter("End", adDate, adParamInput))
End With
With Command
.Parameters("Type").Value = ActivityType
.Parameters("Begin").Value = DateSerial(vYear, vMonth, vPriorDay)
.Parameters("End").Value = DateSerial(vYear, vMonth, vPriorDay + 1)
End With
I also recommend using parameters for insert and update statements. Removing special characters and formatting date strings should never be an issue.
Hope this helps.
Timmus |
|
|
| |
| |
|
Re: Date Formatting for Grid Queries w/UTC Support (Again)
Posted: 04 Aug 07 9:17 PM
|
From the Screen:
ISO_FromDate = Application.basicfunctions.DateToISO (dteFrom.DateTime)
ISO_ToDate = Application.basicfunctions.DateToISO (dteTO.DateTime)
sqlSELECT = "SELECT A1.*, " _
& " (SUBSTRING(CONVERT(VARCHAR(8), STARTDATE - (GETUTCDATE() - GETDATE()), 112),5,2) + ' ' + " _
& " SUBSTRING(CONVERT(VARCHAR(8), STARTDATE - (GETUTCDATE() - GETDATE()), 112),7,2) + ' ' + " _
& " SUBSTRING(CONVERT(VARCHAR(8), STARTDATE - (GETUTCDATE() - GETDATE()), 112),1,4) ) " _
& " + ' ' + DATENAME(dw,CONVERT(VARCHAR(8), STARTDATE - (GETUTCDATE() - GETDATE()), 112)) as STRSTARTDATE "
sqlFROM = " FROM ACTIVITY A1 LEFT OUTER Join ACCOUNT A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) "
sqlWHERE = " WHERE A1.STARTDATE >= '" & ISO_FromDate & "' AND A1.STARTDATE <= '" & ISO_ToDate & "' "
|
|
|
|
Re: Date Formatting for Grid Queries w/UTC Support (Again)
Posted: 07 Aug 07 1:43 PM
|
Thanks all, and I now have a very simple way to handle these date based queries which no longer depend upon the workstations Regional short date format. Thanks to RJ, I'm now using the following code:
Sub psResetTicklerGrid
'** INITIALIZE / RESET THE ACTIVITIES GRID **
lblDisplayDate.Caption = sStartDate
'** Date Format must be as per UTC Support [CCYYMMDD hh:mm:ss] (see LAN Dev. Ref v7.0 on p. 165)
sStartDate = Application.BasicFunctions.DateToISO(sStartDate)
sEndDate = Application.BasicFunctions.DateToISO(sEndDate)
sWQL = sStartDate & "') AND (A1.STARTDATE < '" & sEndDate
sQL = sPQL & sWQL & sZQL
lblCurrentDisplay.Caption = sCurrentDisplay
dgTickler.SQL.Text = sQL
dgTickler.Refresh
End Sub
sWQL is just a partial 'Where' clause which gets put together with a preset select prefix clause and a preset suffix clause (for the 'Order By' parameters). Again, thanks for the help. Also, ever since I first used Basic, I've alway made use of the "Format" command. But Microsoft in all their wisdom has taken it away. Why did they have to do that?
 |
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|