Help with SQL Sentence. DateFormat.

[JuanjoZun]

Hello Again.

I have a problem pulling data from a database... I have the table 'regent' and a field fecha that has 'dd/mm/yyyy' format (I guess). when I try to make a query from this table, the Date (fecha) come in 'mm/dd/yyyy' format. Here is short explanation of my page. I tried to use Session.LCID.

<% @LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot; %>
<% Session.LCID = 2058 %>

<%  fecha_Ini = Request.form(&quot;ex4&quot;)
    fecha_fin = Request.Form(&quot;ex5&quot;)
    f_ini = formatdatetime (cdate(fecha_ini),2) 'dd/mm/yyyy format
    f_fin = formatdatetime (cdate(fecha_fin),2)
  
set conn=Server.CreateObject(&quot;ADODB.Connection&quot;)

constr = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\[URL unfurl="true"]wwwroot\rpersonal1\personal.mdb&quot;[/URL] & &quot;;Persist Security Info=False&quot;

conn.Open constr
Response.Write &quot;LCID DB: &quot; & conn.Properties(&quot;Locale Identifier&quot;) & &quot; LCID Page: &quot; & session.LCID
'LCID from DB is 1034 session is 2058

set rs_f=Server.CreateObject(&quot;ADODB.recordset&quot;)
sqltxt_f = &quot;SELECT regent.fecha as fec &quot;_
         & &quot;FROM regent RIGHT JOIN d_gen ON regent.cod = d_gen.cod &quot;_
         & &quot;GROUP BY regent.fecha &quot;_
	 & &quot;HAVING (((regent.fecha) between #&quot; & f_ini & &quot;# And #&quot; & f_fin & &quot;#))&quot;

Response.Write sqltxt_f & &quot;<br>&quot;
rs_f.Open sqltxt_f, conn,1,2
'Here, takes i.e. f_ini=05/05/2003 (May 5,2003) 
'f_fin=08/05/2003 (May 8, 2003)....
'And It takes: May 5, 2003 and Aug 5, 2003

Is there a way to Format the Date from SQL sentence, or conn.open?

Follow the dark side, so you can reach the light. [/color}

 

[JuanjoZun]

Here is How I solve my problem, Hope this will be useful for more people...

<% 
Private Function SwapDate(byVal dateinput)
	Dim strWorkingDate, tmpArray, assemblystring, del, i
	strWorkingDate = dateinput
	if instr(strWorkingDate, &quot;/&quot;) Then
		tmpArray = split( strWorkingDate, &quot;/&quot; )  :  del = &quot;/&quot;
	elseif instr(strWorkingDate, &quot;-&quot;) Then
		tmpArray = split( strWorkingDate, &quot;-&quot; )  :  del = &quot;-&quot;
	elseif instr(strWorkingDate, &quot; &quot;) then
		tmpArray = split( strWorkingDate, &quot; &quot; )  :  del = &quot; &quot;
	end if
	for i = 0 to ubound(tmpArray)
		tmpArray( i ) = replace( tmparray( i ), &quot;,&quot;, &quot;&quot; )
	next
	assemblystring = tmpArray(1) & del & tmpArray(0)
	if del = &quot; &quot; then assemblystring = assemblystring & &quot;,&quot;
	assemblystring = assemblystring & del & tmpArray(2)
	SwapDate = Trim( assemblystring )
End Function

%>

If you send mm/dd/yyyy, you'll get dd/mm/yyyy and viceversa... very interesting thing, so, when I make my query, this function "set the date" in mm/dd/yyyy (format that Database knows) and I get the Query... very good


Follow the dark side, so you can reach the light. [/color}