|
| |
|
|
sourkrouse
Posts: 26
Joined: 10/13/2007
Status: offline
 |
Multiple Date formats saved to one DB column - 4/3/2008 15:33:19
Hello,
I thought instead of doing hours or reading I would send a post and see what happens. You guys are the best and have been so helpful.
I am sending a form with 2 selections (StartDate and EndDate) to a second page with the following SQL query:
<%
Dim selection, selection2
selection = DateValue(request.form("StartDate2"))
selection2 = DateValue(request.form("EndDate2"))
'Create and establish data connection
Set DataConn = Server.CreateObject("ADODB.Connection")
ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=xxx"
DataConn.ConnectionTimeout = 100
DataConn.Open ConnectionString
'Create and execute SQL queries
SQLString = "SELECT * FROM T_WLWC WHERE ([RECV_DT] BETWEEN '" & selection & "' AND '" & selection2 & "') AND GRP_NM <> 'Branch' AND GRP_NM <> 'Other - Corporate' AND CNTCT_CTR_NM <> 'FA_Tracking'"
Set RS = DataConn.Execute(SQLString)
AltColor="#FFFFFF"
While Not RS.EOF
%>
The problem is that the RECV_DT column contains different formats of dates, some as datetime, some just as date. Is there a way to convert the value and THEN run the query? Of course my selections are already converted but that doesn't help if what it's comparing it against is NOT converted.
I tried this but it says "DateValue function not found":
"SELECT * FROM T_WLWC WHERE (DateValue[RECV_DT] BETWEEN '" & selection & "' AND '" & selection2 & "') AND GRP_NM <> 'Branch' AND GRP_NM <> 'Other - Corporate' AND CNTCT_CTR_NM <> 'FA_Tracking'"
Thanks for any help in advance. The query works but it cuts off any dates with a time on the end date. The work around is the pull a start date to an end date on the day after you need it (like 03/01-04/01 instead of 03/01-03/31) This way is pulls them all and then delete what you don't need.
OR is there a way to pull a month when the dates stored in the DB are different formats?
|
|
|
|
rdouglass
Posts: 9167
From: Biddeford, ME USA
Status: offline
|
RE: Multiple Date formats saved to one DB column - 4/3/2008 15:43:19
quote:
The work around is the pull a start date to an end date on the day after you need it
I'd try working off that just a little more. Why not set the end date to the next day at just after midnight and subtract 1 second from it? Would that work?
selection2 = DateAdd("s",-1,DateAdd("d",1,FormatDateTime(request.form("EndDate2"),VbShortDate)))
I think that's what you'd use. That should get you to 11:59:59 PM on that last date. That help any?quote:
selection2 = DateValue(request.form("EndDate2"))
< Message edited by rdouglass -- 4/3/2008 23:00:13 >
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sourkrouse
Posts: 26
Joined: 10/13/2007
Status: offline
|
RE: Multiple Date formats saved to one DB column - 4/3/2008 17:11:36
quote:
selection2 = DateAdd("s",-1,DateAdd("d",1,FormatDateTime(request.form("EndDate2"),VbShortDate)))
That worked perfect!! Thank you so much. You are awesome!!
Til we meet again....Laura
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
