format date issue

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

format date issue

Logged in as: Guest

arrSession:exec spGetSession 2,3,56401

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> ASP >> format date issue
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

format date issue - 2/6/2008 10:57:02 AM

tombino

Posts: 2
Score: 0
Joined: 2/6/2008
Status: offline

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

Post #: 1

RE: format date issue - 2/6/2008 4:28:08 PM

centauricw

Posts: 10
Score: 0
Joined: 2/20/2006
Status: offline

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,

(in reply to tombino)