tombino
-
Total Posts
:
2
- Scores: 0
-
Reward points
:
0
- Joined: 2/6/2008
-
Status: offline
|
format date issue
Wednesday, February 06, 2008 11:57 AM
( permalink)
Hi,
I have a problem doing a query in ASP. This is my query:
Dim vardate
vardate = "#" & latestdate & "#"
sSQL1 = "SELECT tbl_size.sizename, tbl_stylesize.sizeid, tbl_stylesize.rate, tbl_stylesize.changedate " & _
"FROM tbl_stylesize " & _
"INNER JOIN tbl_size " & _
"ON tbl_stylesize.sizeid = tbl_size.sizeid " & _
"WHERE tbl_stylesize.active = -1 " & _
" AND FORMAT (vardate, "mm/dd/yyyy" ) = tbl_stylesize.changedate " & _
" AND tbl_stylesize.styleid = " & arrItem & _
" ORDER BY tbl_size.sizeid"
The problem is the " (ando may be also /)in the FORMAT date. It reconize it like a end of statement. I tryed to write
"" & mm & "/" & dd & "/" & yyyy & """ or
Chr(34) & mm/dd/yyyy & Chr(34)
but anything is working.
Can you please help me?
Best Regards,
Tom
|
|
|
|
centauricw
-
Total Posts
:
42
- Scores: 2
-
Reward points
:
0
- Joined: 2/20/2006
-
Status: offline
|
RE: format date issue
Wednesday, February 06, 2008 5:28 PM
( permalink)
Here is how craft your SQL query correctly: sSQL1 = "SELECT tbl_size.sizename, tbl_stylesize.sizeid, tbl_stylesize.rate, tbl_stylesize.changedate " & _
"FROM tbl_stylesize " & _
"INNER JOIN tbl_size " & _
"ON tbl_stylesize.sizeid = tbl_size.sizeid " & _
"WHERE tbl_stylesize.active = -1 " & _
" AND '" & FormatDate(vardate, vbShortDate) & "' = tbl_stylesize.changedate " & _
" AND tbl_stylesize.styleid = " & arrItem & _
" ORDER BY tbl_size.sizeid"
Notice I took the SQL FORMAT command out because you actually crafting a SQL query with a literal date (the variable "vardate" is a VBScript variable, not a SQL variable). I inserted the VBScript DateFormat command to return a short date string that SQL Server will understand and I surrounded the date with single-quotes to tell SQL this is a literal value.
Regards,
|
|
|
|
tombino
-
Total Posts
:
2
- Scores: 0
-
Reward points
:
0
- Joined: 2/6/2008
-
Status: offline
|
RE: format date issue
Thursday, February 07, 2008 11:21 AM
( permalink)
Thank you centauricw, this solved my problem. I didnt know the existence of vbshortdate, I am pretty new in VbScript. Best Regards, Tom
|
|
|
|