Hi all,
I have this code which uses an SQL query to extract fields from a table into a .csv file and it works. The issue is there are two date fields in the query and they are be converted to US date format when they should be UK. The date format on the 2008 server is set to united kingdom and the machine has been restarted.
This code was running on a 2003 server and it worked fine on there. The two date fields are last_hs and next_hs. Does anyone have any ideas how i can code them to print out in UK format?
I have this code which uses an SQL query to extract fields from a table into a .csv file and it works. The issue is there are two date fields in the query and they are be converted to US date format when they should be UK. The date format on the 2008 server is set to united kingdom and the machine has been restarted.
This code was running on a 2003 server and it worked fine on there. The two date fields are last_hs and next_hs. Does anyone have any ideas how i can code them to print out in UK format?
Code:
<%@ Language=VBScript %>
<!--#include file="../../includes/connection.asp"-->
<%
sub Write_CSV_From_Recordset( RS )
if RS.EOF then
'
' There is no data to be written
'
exit sub
end if
dim RX
set RX = new RegExp
RX.Pattern = "\r|\n|,|"""
dim i
dim Field
dim Separator
'
' Writing the header row (header row contains field names)
'
Separator = ""
for i = 0 to RS.Fields.Count - 1
Field = RS.Fields( i ).Name
if RX.Test( Field ) then
'
' According to recommendations:
' - Fields that contain CR/LF, Comma or Double-quote should be enclosed in double-quotes
' - Double-quote itself must be escaped by preceeding with another double-quote
'
Field = """" & Replace( Field, """", """""" ) & """"
end if
Response.Write Separator & Field
Separator = ","
next
Response.Write vbNewLine
'
' Writing the data rows
'
do until RS.EOF
Separator = ""
for i = 0 to RS.Fields.Count - 1
'
' Note the concatenation with empty string below
' This assures that NULL values are converted to empty string
'
Field = RS.Fields( i ).Value & ""
if RX.Test( Field ) then
Field = """" & Replace( Field, """", """""" ) & """"
end if
Response.Write Separator & Field
Separator = ","
next
Response.Write vbNewLine
RS.MoveNext
loop
end sub
'
' EXAMPLE USAGE
'
' - Open a RECORDSET object (forward-only, read-only recommended)
' - Send appropriate response headers
' - Call the function
'
dim RS1
set RS1 = Server.CreateObject( "ADODB.RECORDSET" )
qry = ";with cteDelegates(edrs_no, company, postalcode, last_hs, next_hs, risk_level, sct1, sct2, training_provider, rown) as(Select wce_course_delegate_link.edrs_no, wce_contact.company, postalcode, wce_contact.last_hs, wce_contact.next_hs, wce_contact.risk_level, wce_course_delegate_link.sct1, wce_course_delegate_link.sct2, wce_course_delegate_link.training_provider, ROW_NUMBER() OVER (PARTITION BY wce_course_delegate_link.edrs_no ORDER BY wce_course_delegate_link.edrs_no, wce_course_delegate_link.sct1, wce_course_delegate_link.training_provider) from wce_course_delegate_link left join wce_contact on wce_course_delegate_link.edrs_no =wce_contact.edrs where wce_course_delegate_link.end_date is null and wce_contact.record_type= 'company') Select edrs_no, company, postalcode, last_hs, next_hs, risk_level, sct1, sct2, training_provider from cteDelegates where rown = 1 order by edrs_no"
Set RS1 = connStr.Execute(qry)
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment;filename=export.csv"
Write_CSV_From_Recordset RS1
%>
