date time formatting

W

Walter

Hello, I am a newbie to this. I have the following code.
I have a query which gets the data from the SQL Server. On the server the
date is formatted as 'yyyy-MM-dd HH:MM:SS'
If I run the application, the date is being displayed as 'MM/dd/yyyy
HH:MM:SS AM/PM'

Is there anyone who can help me to get the date into the right format.

Thanks

/*** Start code ***/
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

Sub Page_Load(Sender As Object, E As EventArgs)



End Sub

sub TextBox1_TextChanged(Sender As Object, E As EventArgs)

Datalist1.DataSource = GetInfoSSCC(mid(TextBox1.text, 3, 18))
DataList1.DataBind()

end sub

Sub ApplyFilter_Click(Sender As Object, E As EventArgs)

Datalist1.DataSource = GetInfoSSCC(mid(TextBox1.text, 3, 18))
DataList1.DataBind()

End Sub

Function GetInfoSSCC(ByVal sSCC As String) As System.Data.DataSet
Dim connectionString As String = "server='OLNSQL03'; user
id='sa'; password=''; database='APPL_WTP'"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT
[INFO_SSCC].[SSCC],[INFO_SSCC].[ITEM_CODE],[INFO_SSCC].[CASECOUNT],[INFO_SSCC].[LANDCODE]," & _

"[INFO_SSCC].[BESTEMMINGSCODE],[INFO_SSCC].[PRODUCTION_DATE],[INFO_SSCC].[PROD_DATE_LAST]," & _

"[INFO_SSCC].[BEST_BEFORE_DATE],[INFO_SSCC].[LOG_DATE],[INFO_SSCC].[BATCH_CODE],[INFO_LIJN].[SKEPCODE]," & _
"[INFO_STATUS].[STATUS_OMSCHRIJVING] FROM [INFO_SSCC] INNER JOIN " & _
"[INFO_LIJN] ON [INFO_SSCC].[LINE_ID] = [INFO_LIJN].
Code: 
 INNER JOIN "
& _
"[INFO_STATUS] ON [INFO_SSCC].[SSCC_STATUS] =
[INFO_STATUS].[STATUS_CODE] " & _
"WHERE ([INFO_SSCC].[SSCC] = @SSCC)"

Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_sSCC As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_sSCC.ParameterName = "@SSCC"
dbParam_sSCC.Value = sSCC
dbParam_sSCC.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_sSCC)

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function

</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h2>SSCC Info
</h2>
<hr size="1" />
<form runat="server">
<p>
<asp:TextBox id="TextBox1" runat="server"
OnTextChanged="ApplyFilter_Click" AutoPostBack="True"></asp:TextBox>
&nbsp;
<asp:Button id="Button1" onclick="ApplyFilter_Click"
runat="server" Text="Get Info"></asp:Button>
</p>
<p>
<asp:DataList id="DataList1" runat="server">
<ItemTemplate>
<p>
SSCC : <asp:Label id="Label1" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"SSCC") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Pallet Status : <asp:Label id="Label12"
runat="server" text='<%#
DataBinder.Eval(Container.DataItem,"STATUS_OMSCHRIJVING") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
ItemCode : <asp:Label id="Label2" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"Item_Code") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
CaseCount : <asp:Label id="Label3" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"CaseCount") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
LandCode : <asp:Label id="Label4" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"LandCode") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Bestemming : <asp:Label id="Label5" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"BestemmingsCode") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
First case : <asp:Label id="Label6" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"Production_Date") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Last case : <asp:Label id="Label7" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"Prod_Date_Last") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Best Before Date : <asp:Label id="Label8"
runat="server" text='<%#
DataBinder.Eval(Container.DataItem,"BEST_BEFORE_DATE") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Print Date : <asp:Label id="Label9" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"LOG_DATE") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Batchcode : <asp:Label id="Label10" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"BATCH_CODE") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Line ID : <asp:Label id="Label11" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"SKEPCODE") %>'
forecolor="#0000C0"></asp:Label>
</p>
</ItemTemplate>
</asp:DataList>
</p>
</form>
</body>
</html>
/*** End Code ***/
 
P

Prasanth Raveendran

yes. you please change the query and convert that date field to a varchar
field. for example:-

Select Convert(varchar(10),[INFO_SSCC].[PROD_DATE_LAST],103) from table
.......

it will return date as MM/dd/yyyy and try for SQL documentation for Convert
Function. and please visit the following page, it will clear all ur doubts

http://www.aspfaq.com/show.asp?id=2260

and in this site, pls vist other pages also.

gud luck, thank you



Walter said:
Hello, I am a newbie to this. I have the following code.
I have a query which gets the data from the SQL Server. On the server the
date is formatted as 'yyyy-MM-dd HH:MM:SS'
If I run the application, the date is being displayed as 'MM/dd/yyyy
HH:MM:SS AM/PM'

Is there anyone who can help me to get the date into the right format.

Thanks

/*** Start code ***/
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

Sub Page_Load(Sender As Object, E As EventArgs)



End Sub

sub TextBox1_TextChanged(Sender As Object, E As EventArgs)

Datalist1.DataSource = GetInfoSSCC(mid(TextBox1.text, 3, 18))
DataList1.DataBind()

end sub

Sub ApplyFilter_Click(Sender As Object, E As EventArgs)

Datalist1.DataSource = GetInfoSSCC(mid(TextBox1.text, 3, 18))
DataList1.DataBind()

End Sub

Function GetInfoSSCC(ByVal sSCC As String) As System.Data.DataSet
Dim connectionString As String = "server='OLNSQL03'; user
id='sa'; password=''; database='APPL_WTP'"
Dim dbConnection As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT
[INFO_SSCC].[SSCC],[INFO_SSCC].[ITEM_CODE],[INFO_SSCC].[CASECOUNT],[INFO_SSCC].[LANDCODE]," & _

"[INFO_SSCC].[BESTEMMINGSCODE],[INFO_SSCC].[PRODUCTION_DATE],[INFO_SSCC].[PROD_DATE_LAST]," & _

"[INFO_SSCC].[BEST_BEFORE_DATE],[INFO_SSCC].[LOG_DATE],[INFO_SSCC].[BATCH_CODE],[INFO_LIJN].[SKEPCODE]," & _
"[INFO_STATUS].[STATUS_OMSCHRIJVING] FROM [INFO_SSCC] INNER JOIN " & _
"[INFO_LIJN] ON [INFO_SSCC].[LINE_ID] = [INFO_LIJN].
Code: 
 INNER JOIN "
& _
"[INFO_STATUS] ON [INFO_SSCC].[SSCC_STATUS] =
[INFO_STATUS].[STATUS_CODE] " & _
"WHERE ([INFO_SSCC].[SSCC] = @SSCC)"

Dim dbCommand As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_sSCC As System.Data.IDataParameter = New
System.Data.SqlClient.SqlParameter
dbParam_sSCC.ParameterName = "@SSCC"
dbParam_sSCC.Value = sSCC
dbParam_sSCC.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_sSCC)

Dim dataAdapter As System.Data.IDbDataAdapter = New
System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function

</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h2>SSCC Info
</h2>
<hr size="1" />
<form runat="server">
<p>
<asp:TextBox id="TextBox1" runat="server"
OnTextChanged="ApplyFilter_Click" AutoPostBack="True"></asp:TextBox>

<asp:Button id="Button1" onclick="ApplyFilter_Click"
runat="server" Text="Get Info"></asp:Button>
</p>
<p>
<asp:DataList id="DataList1" runat="server">
<ItemTemplate>
<p>
SSCC : <asp:Label id="Label1" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"SSCC") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Pallet Status : <asp:Label id="Label12"
runat="server" text='<%#
DataBinder.Eval(Container.DataItem,"STATUS_OMSCHRIJVING") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
ItemCode : <asp:Label id="Label2" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"Item_Code") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
CaseCount : <asp:Label id="Label3" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"CaseCount") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
LandCode : <asp:Label id="Label4" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"LandCode") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Bestemming : <asp:Label id="Label5" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"BestemmingsCode") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
First case : <asp:Label id="Label6" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"Production_Date") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Last case : <asp:Label id="Label7" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"Prod_Date_Last") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Best Before Date : <asp:Label id="Label8"
runat="server" text='<%#
DataBinder.Eval(Container.DataItem,"BEST_BEFORE_DATE") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Print Date : <asp:Label id="Label9" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"LOG_DATE") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Batchcode : <asp:Label id="Label10" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"BATCH_CODE") %>'
forecolor="#0000C0"></asp:Label>
</p>
<p>
Line ID : <asp:Label id="Label11" runat="server"
text='<%# DataBinder.Eval(Container.DataItem,"SKEPCODE") %>'
forecolor="#0000C0"></asp:Label>
</p>
</ItemTemplate>
</asp:DataList>
</p>
</form>
</body>
</html>
/*** End Code ***/[/QUOTE]
Click to expand...
 
W

Walter

Well that was my first idea. It would look like this then:

PS Modified layout to read beter

Dim queryString As String = "SELECT [INFO_SSCC].[SSCC],
[INFO_SSCC].[ITEM_CODE],
[INFO_SSCC].[CASECOUNT],

[INFO_SSCC].[LANDCODE]," & _
"[INFO_SSCC].[BESTEMMINGSCODE],

Convert(varchar(19),[INFO_SSCC].[PRODUCTION_DATE],121),

Convert(varchar(19),[INFO_SSCC].[PROD_DATE_LAST],121) ," & _

"Convert(varchar(19),[INFO_SSCC].[BEST_BEFORE_DATE],121),

Convert(varchar(19),[INFO_SSCC].[LOG_DATE]121),
[INFO_SSCC].[BATCH_CODE],

[INFO_LIJN].[SKEPCODE]," & _

"[INFO_STATUS].[STATUS_OMSCHRIJVING] FROM [INFO_SSCC] INNER JOIN " & _
"[INFO_LIJN] ON
[INFO_SSCC].[LINE_ID] = [INFO_LIJN].
Code: 
 INNER JOIN " & _
"[INFO_STATUS] ON
[INFO_SSCC].[SSCC_STATUS] =  [INFO_STATUS].
[STATUS_CODE] " & _
"WHERE ([INFO_SSCC].[SSCC] =
@SSCC)"

But then I get the error that the variable PRODUCTION_DATE is not know..
 
P

Prasanth Raveendran

Simple..... you have not used field aliasing thats y error is coming. try
following. I am posting the query only.... u concatenate as per ur needs.


SELECT [INFO_SSCC].[SSCC],
[INFO_SSCC].[ITEM_CODE],
[INFO_SSCC].[CASECOUNT],
[INFO_SSCC].[LANDCODE],
[INFO_SSCC].[BESTEMMINGSCODE],
Convert(varchar(19),[INFO_SSCC].[PRODUCTION_DATE],121) AS [PRODUCTION_DATE],
Convert(varchar(19),[INFO_SSCC].[PROD_DATE_LAST],121) AS [PROD_DATE_LAST ,
Convert(varchar(19),[INFO_SSCC].[BEST_BEFORE_DATE],121) AS
[BEST_BEFORE_DATE],
Convert(varchar(19),[INFO_SSCC].[LOG_DATE],121) AS [LOG_DATE],
[INFO_SSCC].[BATCH_CODE],
[INFO_LIJN].[SKEPCODE],
[INFO_STATUS].[STATUS_OMSCHRIJVING]
FROM [INFO_SSCC]
INNER JOIN [INFO_LIJN]
ON [INFO_SSCC].[LINE_ID] = [INFO_LIJN].
Code: 
INNER JOIN [INFO_STATUS]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

repeater 0
Issue getting data back from textbox in Datalist and container 0
Problem with Data Grid 0
DetailsView Controls error 0
Server Error in '/' Application. 2
Problem with Data Grid 0
Datalist is overlapping other controls 0
GridView Question 1

Members online

No members online now.
Total: 30 (members: 0, guests: 30)
Robots: 456

Forum statistics

Threads
473,768
Messages
2,569,574
Members
45,051
Latest member
CarleyMcCr

Latest Threads

Top