Multiple Date formats saved to one DB column (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

sourkrouse -> 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 -> 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"))



sourkrouse -> 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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.046875