Combining fields in select statement

[davg]

I'm having problem running a VBScript routine to combine separate YYYY, MM, DD fields into one field and then validate if between selected dates;

strFromDate = InputBox("Enter From Date:&quot
strToDate = InputBox("Enter To Date:&quot

Select AYear + AMonth + ADay as ADate, Field1, Field 2, Field3 FROM Table WHERE ADate BETWEEN " & strFromDate & " and " & strToDate & "

As I'm faily new to SQL can anyone help with the syntax?

 

[MakeItSo]

Hi Davg!

You could give the DateValue() function a try:
Select DateValue(AYear & "/" + AMonth & "/" + ADay) as ADate, Field1, Field 2, Field3 FROM Table WHERE ADate BETWEEN " & strFromDate & " and " & strToDate & "

If this does not work, you might also need to embed strfromdate and strtodate in DateValue().

Hope this helps,
MakeItSo


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[davg]

Many thanks for the quick response.

I'm getting the error '[Microsoft][ODBC SQL Server Driver][SQL Server]'DateValue' is not a recognized function name.

Here's the entire script which is saved as a .vbs
_____________________________________________________

Dim fsoOutput, fsoStream, objConnection, objRecords
Dim strName, strFields
Dim strFromDate, strToDate

strMsg = "Sage Export"
strFromDate = InputBox("Enter From Date: YYYY-MM-DD", strMsg)
strToDate = InputBox("Enter To Date: YYYY-MM-DD", strMsg)

Set fsoOutput = CreateObject("Scripting.FileSystemObject&quot
Set objConnection = CreateObject("ADODB.Connection&quot


objConnection.Open = "DSN=TabTag;UID=sa;PWD=tabtag;Database=TABTAG;"

Set objRecords = objConnection.Execute("Select DateValue(AAPickupYear & "-" + AAPickupMonths & "-" + AAPickupDays) as Pickup, AAWebRefNo, AACompanyName, AASubtotal, AAVATPCent, AADocketNo, AAContactName FROM SQLViewUserDefined WHERE Pickup BETWEEN " & strFromDate & " and " & strToDate & "&quot

For Each strName In objRecords.Fields
strFields = strFields + strName.Name + ","
Next

Set fsoStream = fsoOutput.CreateTextFile("c:\Data\output.csv", True)

fsoStream.WriteLine Left(strFields, Len(strFields) - 1)
fsoStream.WriteLine Replace(objRecords.GetString, vbTab, ",&quot

objConnection.Close
___________________________________________________________