Query On Datetime Field Giving Eroneous Result
Mar 20, 2008
Hi,
I am trying to fetch some data from a table .
I wanted to fetch data for which the order_date is >= 1st january 2005 and order_date< 10th January 2005 and for that I have a query
like
1>
SELECT * FROM SALES_ORDER WHERE
ORDER_DATE >= '01/01/2005' and ORDER_DATE <'10/01/2005'
In this query i am getting result for which the order_date is 2005-05-27 and lots of other data for which my criteria is not matching.
The Order_Date is a datetime field.
If I am use convert function then i am getting the expected result
like
2>
SELECT * FROM SALES_ORDER WHERE
convert (datetime, convert (varchar,ORDER_DATE,101), 101) >= '01/01/2005'
AND
convert (datetime, convert (varchar,ORDER_DATE,101), 101) < '01/10/2005'
Why the first query is not giving me correct result ?
View 4 Replies
ADVERTISEMENT
Mar 13, 2008
Good morning,
I'm trying to create a view on SQL Server based on a query that i use on Access. The query returns detailed data from a table based on ID's selected and saved into another table.
the thing is, on access it returns back 5 records<which is a correct output> , while on SQL. its returning only one record. what could be the issue here ?
thank you,
View 3 Replies
View Related
May 30, 2006
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields.
View 17 Replies
View Related
Oct 24, 2007
Hello,
I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.
The report language is "English (United Kingdom)"
The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.
The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.
However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).
I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.
Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?
View 3 Replies
View Related
Dec 2, 2015
I have a table of errors with a DateTime field for when the error occurred. I want to query the table for a given date range omitting the time portion. What is the most efficient way to perform this query?
View 5 Replies
View Related
Mar 14, 2007
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies
View Related
May 4, 2006
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
View 3 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Oct 17, 2007
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
View 1 Replies
View Related
Nov 17, 2012
DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
View 2 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 Replies
View Related
Apr 14, 2008
I am trying to do a query similar to this:
SELECT * FROM TRANSACCTION_HISTORY WHERE TXN_DATE=@RepDate
For the query parameter @RepDate, I would like to pass a function to it. The function is a calculation of date based on today's date (e.g. Today() function to be simple). So that users don't have to type in the date every time.
Now the question is: is it really possible to do that in RS? Because I am getting a type conversion error, when I put Today() in the Define Query Parameters disalog box.
Thanks for helping!
View 1 Replies
View Related
Mar 30, 2006
I got the following code and I like to give the label.text a value which is selected by the query in the .commandtext line. This is off course not working but I don't have any idea how it can work. Someone suggestions??Thanks!!Partial Class ShowBonsaiProperties Inherits System.Web.UI.Page Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("BonsaiDataBaseConnectionString").ToString()) Dim cmd As New System.Data.SqlClient.SqlCommand With cmd .CommandType = System.Data.CommandType.Text .CommandText = "SELECT BonsaiName FROM Bonsai WHERE BonsaID = 1" .Connection = sqlConnection2 End With sqlConnection2.Open() cmd.ExecuteNonQuery() sqlConnection2.Close() Dim BonsaiNameText As Object = .CommandText LblShowBonsaiName.Text = BonsaiName End SubEnd Class
View 13 Replies
View Related
Mar 17, 2007
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View 2 Replies
View Related
Mar 11, 2008
help
i need to check each column
and if i don't have the value
than add to a new result row
if not show the missing value
if i have in the field A B C than OK (like day1 + day2)
if i don't have the value A B C than than show it like this a|b|c (like day3)
if i don't have the value c than than show it like this -|-|c (like day4)
if i don't have the value a than than show it like this a|-|- (like day5)
if i don't have the value b than than show it like this -|b|- (like day6)
day1 day2 day3 day4 day5 day6
-------------------------------------------------------------------------
* c * a c a
* a * a c *
a b # b b c
b * $ Q c s
c # 0 0 0 *
---------------------------------------------------------------------new result row
ok ok a|b|c -|-|c a|-|- -|b|-
TNX
View 7 Replies
View Related
Aug 20, 2005
Hii,I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?use northwind
select city, region,([city]+ +[region]) as uniqefrom customerswhere region is not nullThe resulting records in Quary Anchorage AK AnchorageAKTsawassen BC TsawassenBCVancouver BC VancouverBCSan Francisco CA San FranciscoCA
View 1 Replies
View Related
Sep 12, 2006
I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier. Here is few result samples I receive from this code I am using now.
ReqNo ProcID
7102005 1409
7102005 1796
7139003 1411
7139003 6097
7261030 1409
7261030 1796
7268303 3998
7268303 4000
I would like to create a single row for each "ReqNo" and have a field that will an array of the "ProcID" results I receive. In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field? I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table.
So, ideally my result would be:
ReqNo ProcSet
7102005 1409, 1796
7139003 1411, 6097
7261030 1409, 1796
7268303 3998, 4000
Is this possible?
declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
select
@startdate = dateadd (dd, -7, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
select
@enddate = dateadd (dd, 1 , @startdate)
select distinct
pp_req_no as ReqNo,
pp_cproc_id_r as ProcID
from
risdb_rch08_stag..performed_procedure
(index pp_serv_time_r_ndx)
where
pp_service_time_r between @Startdate and @Enddate
and pp_status_v = 'CP'
and pp_rep_id > 0
order by
pp_req_no, pp_cproc_id_r
View 4 Replies
View Related
Jul 17, 2007
Hi,
I have one dymic report and one of the requirement is that,I want to check whether particular field is present there or not
For e.g In one of the case Fields!Name will come and in other case it won't.
There one dynmic column which calculated values based on all the column.
If I write in value field of this column as follows.[If status = "Name" and Field is not missing then take Name value,if field name is Consumer take value from Consumer and shows it in column.]
=Switch(Fields!portfolio_status.Value="Name",IIF(Fields!IName.IsMissing,"0",Fields!Name.Value)
,Fields!portfolio_status.Value="Consumer",IIF(Fields!Consumer.IsMissing,"0",Fields!Consumer.Value))
In case,portfolio_status doesn't give me value "consumer" then I expect "0" to get printed.Problem here is that IIF expression tries to evaluate Fields!Consumer.Value even if it is not using this value and because of that my calculated column is not working.
I tried IsMissing,IsNothing..Nothing seems work.Basically I want to check if that field is there in result set or not.
IIF(IsNothing(Fields!Consumer),"0",Fields!Consumer.Value)
IIF(Fields!Consumer.Value Is Nothing,"0",Fields!Consumer.Value)
View 1 Replies
View Related
Feb 26, 2004
Hello,
I need to show only the date and not the date and time in a datetime field. How do I do this ? EG only store date not date and time.
I want to do this via enterprise manager
Many thanks
Grant
View 2 Replies
View Related
Feb 26, 2001
OUr end-users will be accessing data through a SQL view through third party applications. Ex. Access, Excel, Crystal Reports. All the date fields need to only have 'Data' XX/XX/XXXX, not datetime. Since SQL Server only stores the data as datetime, I convert the data in the proper format in the Create view statement. The only problem is that the data is now a text (varchar(10) field in which sorting through these third party applications causes issues.
Ex. 01/05/2000 comes before 02/02/1999 because it sorts on the first characters. Is there a way in SQL to store the data as a Date only or any other suggestions.
Thanks for your help.
Daniel
View 2 Replies
View Related
Dec 20, 2006
hello there.
i have a databse with a datetime Filed and i want to find all records of the month on the datetie filed.
how can i do it.
i was using this but it does not work
SELECT * FROM laconexion.lc_members WHERE (baja = MONTH(GETDATE()))
thanks a lot
View 4 Replies
View Related
Feb 21, 2014
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below you will find the code I am running:
create table #Items
(
ItemNovarchar (50),
SearchNo varchar (50),
Historical int,
Blocked int
[Code] ....
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1
Where Exists
(
SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END)
--,SearchNo
--,COUNT(*)
[Code] ...
Why is the update changing each record? How can I change the update to choose the correct records?
View 6 Replies
View Related
May 7, 2015
I would like to replace the value in the Select query of CO OWNER with a space ' ' or NULL if the ORDINAL value = 0.
The CO OWNER name is stored in the CARDNAME table, and the OWNER name is stored in the NAME table. I can not change the db structure.
JEAN is the ACCOUNT owner and BILL is CO OWNER of two cards.
Current Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 BILL JEAN
Desired Select Results:
ACCOUNT CARD ORDINAL CO OWNER OWNER
200500 9999999999999100 2 BILL JEAN
200500 9999999999999101 1 BILL JEAN
200500 9999999999999102 0 NULL JEAN
Current SQL Select statement:
SELECT DISTINCT
CARD.PARENTACCOUNT AS ACCOUNT,
CARD.NUMBER AS CARD,
CARD.ORDINAL,
CARD.STATUS,
CARDNAME.FIRST AS CO_OWNER,
[code]....
View 9 Replies
View Related
Feb 7, 2007
I keep getting the following error message but I don't see what's wrong with my code
Server Error in '/Admin' Application.
Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Source Error:
Line 147:cmdSql.Parameters.Add("@event_active","1")
Line 148:
Line 149:cmdSql.ExecuteNonQuery()
Line 150:
Line 151:pnlForm.Visible = FalseSource File: c:hostingwebhost4lifememberNYACOAadmincalendar.aspx Line: 149 Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735078
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
ASP.calendar_aspx.SubmitForm(Object s, EventArgs E) in c:hostingwebhost4lifememberNYACOAadmincalendar.aspx:149
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
1 <%@ Register TagPrefix="cc1" Namespace="ManyMonkeys.Web.HtmlEditorLibrary.Controls" Assembly="HtmlEditorLibrary" %>
2 <%@ Page Language="VB" masterpagefile="nyacoaadmin.master" debug="true" %>
3 <%@ mastertype virtualpath="nyacoaadmin.master" %>
4 <%@ import Namespace="System" %>
5 <%@ import Namespace="System.Data" %>
6 <%@ import Namespace="System.Data.SqlClient" %>
7 <script runat="server">
8
9 Dim Connection As SqlConnection
10 Dim Sql As String
11 Dim cmdSql As SqlCommand
12 Dim dtr As SqlDataReader
13
14 Sub Page_Load
15
16 Connection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
17 Connection.Open()
18
19 If Not isPostback then
20
21 Sql = "SELECT chapter_id, chapter_name FROM chapter ORDER BY chapter_id"
22 cmdSql = New SqlCommand (Sql, Connection)
23 dtr = cmdSql.ExecuteReader()
24
25 ddlChapter.DataSource = dtr
26 ddlChapter.DataTextField = "chapter_name"
27 ddlChapter.DataValueField = "chapter_name"
28 ddlChapter.DataBind()
29
30 dtr.Close
31
32 ddlChapter.Items.Insert(0, New ListItem(" ", ""))
33
34 Call ShowList()
35
36 End If
37
38 End Sub
39
40 Sub Page_Unload
41
42 'Connection.Close()
43
44 End Sub
45
46 Sub ShowList()
47
48 Sql = "SELECT event_id, event_name, event_date, chapter_name FROM event WHERE event_active = '1' ORDER BY event_id"
49 cmdSql = New SqlCommand(sql, Connection)
50 dtr = cmdSql.ExecuteReader()
51
52 dgrdList.DataSource = dtr
53 dgrdList.DataBind()
54 dtr.Close()
55
56 pnlList.Visible = True
57
58 End Sub
59
60 Sub dgrdList_ItemCommand(s As Object, E As DataGridCommandEventArgs)
61
62 If e.CommandName = "edit" then
63 Call EditForm(dgrdList.DataKeys(e.Item.ItemIndex))
64 Else If e.CommandName = "delete" then
65 Call ConfirmDelete(dgrdList.DataKeys(e.Item.ItemIndex))
66 End If
67
68 End Sub
69
70 Sub ConfirmDelete(idVal As Integer)
71
72 pnlList.Visible = False
73 pnlConfirmDelete.Visible = True
74
75 sql = "SELECT event_name FROM event WHERE event_id = " & idVal
76 cmdSql = New SqlCommand(sql, connection)
77 litDelete.Text = cmdSql.ExecuteScalar()
78
79 ViewState("idVal") = idVal
80
81 End Sub
82
83 Sub DeleteForm (s As Object, E As Eventargs)
84
85 sql = "UPDATE event SET event_active = '0' WHERE event_id = " & ViewState("idVal")
86 cmdSql = New SqlCommand(sql, connection)
87 cmdSql.ExecuteNonQuery()
88
89 pnlList.Visible = True
90 pnlConfirmDelete.Visible = False
91
92 Call ShowList()
93
94 End Sub
95
96 Sub ShowCalendar(s As Object, E As ImageClickEventargs)
97
98 pnlEventDate.Visible = True
99
100 End Sub
101
102 Sub UpdateDate (s As Object, E As Eventargs)
103
104 pnlEventDate.Visible = False
105 txtEventDate.text=eventCalendar.SelectedDate.ToShortDateString()
106
107 End Sub
108
109 Sub SubmitForm (s As Object, E As Eventargs)
110
111 If ViewState("submitType") = "edit" then
112 sql = "UPDATE event SET event_name=@event_name, event_date=@event_date, chapter_name=@chapter_name, event_description=@event_description, event_last_updated_by=@event_last_updated_by, event_active=@event_active WHERE event_id = " & ViewState("idVal")
113 Else
114 sql = "INSERT INTO event (event_name, event_date, chapter_name, event_description, event_added_by, event_last_updated_by, event_active) VALUES (@event_name, @event_date, @chapter_name, @event_description, @event_added_by, @event_last_updated_by, @event_active)"
115 End If
116
117 cmdSql = New SqlCommand(sql, Connection)
118
119 If Len(txtEventName.Text) = 0 then
120 cmdSql.Parameters.Add("@event_name",DbNull.Value)
121 Else
122 cmdSql.Parameters.Add("@event_name",txtEventName.Text)
123 End If
124
125 If Len(txtEventDate.Text) = 0 then
126 cmdSql.Parameters.Add("@event_date",DbNull.Value)
127 Else
128 cmdSql.Parameters.Add("@event_date",txtEventDate.Text & " " & ddlEventHour.SelectedValue & ":" & ddlEventMinute.SelectedValue & " " & ddlEventAMPM.SelectedValue)
129 End If
130
131 If Len(ddlChapter.SelectedValue) = 0 then
132 cmdSql.Parameters.Add("@chapter_name",DbNull.Value)
133 Else
134 cmdSql.Parameters.Add("@chapter_name",ddlChapter.SelectedValue)
135 End If
136
137 If Len(txtEventDescription.Text) = 0 then
138 cmdSql.Parameters.Add("@event_description",DbNull.Value)
139 Else
140 cmdSql.Parameters.Add("@event_description",txtEventDescription.Text)
141 End If
142
143 cmdSql.Parameters.Add("@event_added_by",session("admin_username"))
144
145 cmdSql.Parameters.Add("@event_last_updated_by",session("admin_username"))
146
147 cmdSql.Parameters.Add("@event_active","1")
148
149 cmdSql.ExecuteNonQuery()
150
151 pnlForm.Visible = False
152 pnlList.Visible = True
153
154 Call ShowList()
155
156 End Sub
157
158 Sub AddForm(s As Object, E As Eventargs)
159
160 pnlForm.Visible = True
161 pnlList.Visible = False
162
163 txtEventName.Text = ""
164 txtEventDate.Text = ""
165 ddlChapter.ClearSelection()
166 txtEventDescription.Text = ""
167
168 ViewState("submitType") = "add"
169
170 End Sub
171
172 Sub EditForm (idVal As Integer)
173
174 pnlList.Visible = False
175 pnlForm.Visible = True
176
177 sql = "SELECT * FROM event WHERE event_id = " & idVal
178 cmdSql = New SqlCommand(sql, connection)
179 dtr = cmdSql.ExecuteReader()
180
181 If dtr.Read() then
182
183 txtEventName.Text = dtr("event_name") & ""
184 txtEventDate.Text = dtr("event_date") & ""
185 txtEventDescription.Text = dtr("event_description") & ""
186 ddlChapter.ClearSelection()
187 Try
188 ddlChapter.Items.FindByValue(dtr("chapter_name")).Selected = True
189 Catch Ex As Exception
190 End Try
191
192 End If
193
194 dtr.Close()
195
196 ViewState("submitType") = "edit"
197 ViewState("idVal") = idVal
198
199 End Sub
200
201 Sub DeleteCancel (s As Object, E As Eventargs)
202
203 pnlConfirmDelete.Visible = False
204 pnlList.Visible = True
205
206 End Sub
207
208 </script>
209
210 <asp:Content contentplaceholderid="cplhMainContent" runat="server">
211
212 <asp:Panel runat="server" id="pnlList" Visible="False">
213
214 <asp:DataGrid runat="server" id="dgrdList" AutoGenerateColumns="False" CellPadding="3" CellSpacing="0" width="550" Border="0" DataKeyField="event_id" onItemCommand="dgrdList_ItemCommand">
215 <HeaderStyle BackColor="#CDAD00" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Bold="true" Font-Size="10pt" HorizontalAlign="center" ForeColor="white" />
216 <ItemStyle BackColor="white" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Size="10pt" ForeColor="black" VerticalAlign="top" />
217 <AlternatingItemStyle BackColor="#dcdcdc" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Size="10pt" ForeColor="black" VerticalAlign="top" />
218 <Columns>
219 <asp:ButtonColumn Text="Edit" ButtonType="PushButton" CommandName="edit" />
220 <asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="delete" />
221 <asp:BoundColumn DataField="event_name" HeaderText="Event Name" />
222 <asp:BoundColumn DataField="event_date" HeaderText="Date" />
223 <asp:BoundColumn DataField="chapter_name" HeaderText="Sponsoring Chapter" />
224 </Columns>
225 </asp:DataGrid>
226
227 <asp:Button runat="server" Text="Add New Event" onClick="AddForm"></asp:Button>
228
229 </asp:Panel>
230
231 <asp:Panel runat="server" id="pnlForm" Visible="False">
232
233 Name of Event: <asp:TextBox id="txtEventName" runat="server"></asp:TextBox>
234 <br />Chapter: <asp:DropDownList id="ddlChapter" runat="server" AutoPostBack="False" />
235 <br />Date: <asp:TextBox id="txtEventDate" runat="server" ReadOnly="true" BackColor="Gray"></asp:TextBox> <asp:ImageButton id="CalButton" runat="server" OnClick="ShowCalendar" ImageUrl="images/cal.gif" />
236 <br /><asp:Panel runat="server" id="pnlEventDate" Visible="False">
237 <asp:Calendar id="eventCalendar" runat="server" FirstDayOfWeek="Sunday" Font-Names="Tahoma" Font-Size="8pt" OnSelectionChanged="UpdateDate">
238 <SelectedDayStyle BackColor="#EC9900" Font-Bold="True" />
239 <TodayDayStyle BackColor="#FFC0C0" />
240 <SelectorStyle Font-Names="Tahoma" Font-Size="8pt" />
241 <DayStyle Font-Names="Tahoma" Font-Size="8pt" />
242 <WeekendDayStyle BackColor="#E0E0E0" />
243 <OtherMonthDayStyle ForeColor="Silver" />
244 <DayHeaderStyle BackColor="#334B79" Font-Names="Tahoma" Font-Size="8pt" ForeColor="White" />
245 <TitleStyle BackColor="#CCCCFF" Font-Bold="True" Font-Names="Tahoma" Font-Size="10pt" />
246 </asp:Calendar>
247 <br />
248 </asp:Panel>
249 Time: <asp:DropDownList ID="ddlEventHour" runat="server" >
250 <asp:ListItem Text="12" Value="12" Selected="true"/>
251 <asp:ListItem Text="1" Value="1" />
252 <asp:ListItem Text="2" Value="2" />
253 <asp:ListItem Text="3" Value="3" />
254 <asp:ListItem Text="4" Value="4" />
255 <asp:ListItem Text="5" Value="5" />
256 <asp:ListItem Text="6" Value="6" />
257 <asp:ListItem Text="7" Value="7" />
258 <asp:ListItem Text="8" Value="8" />
259 <asp:ListItem Text="9" Value="9" />
260 <asp:ListItem Text="10" Value="10" />
261 <asp:ListItem Text="11" Value="11" />
262 </asp:DropDownList>
263 :
264 <asp:DropDownList ID="ddlEventMinute" runat="server">
265 <asp:ListItem Text="00" Value="00" Selected="true" />
266 <asp:ListItem Text="15" Value="15" />
267 <asp:ListItem Text="30" Value="30" />
268 <asp:ListItem Text="45" Value="45" />
269 </asp:DropDownList>
270 <asp:DropDownList ID="ddlEventAMPM" runat="server">
271 <asp:ListItem Text="AM" Value="AM" />
272 <asp:ListItem Text="PM" Value="PM" Selected="true" />
273 </asp:DropDownList>
274 <br /><br />
275 Content:
276 <br /><cc1:HtmlEditor id="txtEventDescription" runat="server" Height="300px" Width="575px"></cc1:HtmlEditor>
277 <br /><br />
278 <asp:Button onclick="SubmitForm" runat="server" Text="Submit"></asp:Button>
279
280 </asp:Panel>
281
282 <asp:Panel runat="server" id="pnlConfirmDelete" Visible="False">
283
284 Are you sure you wish to delete <asp:Literal runat="server" id="litDelete"></asp:Literal>?
285 <br /><br />
286 <asp:Button runat="server" Text="Yes - DELETE" onClick="DeleteForm"></asp:Button> <asp:Button runat="server" Text="No - GO BACK" onClick="DeleteCancel"></asp:Button>
287
288 </asp:Panel>
289
290 </asp:Content>
View 4 Replies
View Related
Mar 7, 2008
dear friends.................. i have a table like below.. name date ------------------------------------------------------------------------------------------------------------ anbumani 2/18/2008 4:15:56 PM anbumani 2/18/2008 4:21:29 PM anbumani 2/18/2008 4:23:03 PM anbumani 2/18/2008 4:25:25 PM i want a out put as only time (ex : 4:15:56 PM 4:21:29 PM 4:23:03 PM ) give me the stored procedure code in sql server 2005.. thanks and regards Samuel Chandradoss . J
View 3 Replies
View Related
Apr 8, 2008
I looked at this post and it didn't work for me.
Trying to extract the time portion from a datetime field. ex 7:15:12 AM from 1/1/2008 7:15:12 aM
This is what I tried and neither functions give me the results I want....
SELECT DueTime, CONVERT(varchar, DueDate, 108) AS Expr1, SUBSTRING(CAST(DueDate AS varchar), 10, LEN(DueDate)) AS Expr2, CAST(DueDate AS varchar) AS Expr3FROM TODO
Due Time Expr1 Expr2 Expr3
1/1/2008 5:15:00 PM 00:00:00 12:00:00 AM Jan 1 2008 12:00:00 AM
It seems Cast(DueDate as varchar) is the problem it converts the time to the default 12:00:00 AM
In the above example I want 5:15:00 PM
Tx in advance
View 2 Replies
View Related
Apr 22, 2008
Here is my nightmare!! : I have 3 database tables. ( START, STOP, TICK )START ( Start_ID, Start_Time )STOP ( Stop_ID, Stop_Time )TICK ( Tick_ID, Tick_Time ) I have 3 records in each tables with the time fields being in datetime format. What I want to accomplish: I want to compare Tick_Time to see if it is between Start_Time and Stop_Time. ie. IF Tick_Time > Start_Time AND Tick_Time < Stop_Time THEN that Tick_Time.colour = Green elseTick_Time.colour = Red Can datetime fields be easily compared like I have shown above? Else can anyone give me an idea of howwwww Thanks and any help is appreciated
View 6 Replies
View Related
Jun 8, 2008
Hi everyone,
I want to get year, month, day and time parts of a DateTime field in sql,
Can help me?
View 1 Replies
View Related
Jun 13, 2008
How would I go about selecting records where a datetime field is >= 1hr?
View 2 Replies
View Related
Mar 11, 2004
Hi
What is the best practice to get the date from a smalldatetime field without the time.
The table contains 5 minute readings for energy consumption in the column period.
Now i need to get all the readings form some dates.
SELECT dbo.TBL_Data.*
FROM dbo.TBL_Data
WHERE (Period IN (CONVERT(DATETIME, '2003-12-31', 102), CONVERT(DATETIME, '2004-01-01', 102)))
this result contains only the readings for the timestamp 00:00
so how to select the whole day ?
kind regards
piet
View 1 Replies
View Related
Sep 2, 2004
Hi,
I have used smalldatetime datatype to store my date and time values. i want to store just the date or time but the problem is it stores both the date and time. For eg, if i add the the date 03/11/2004, it also the stores the current time automatically. so the new value will be something like 03/11/2004 10:00:00 AM where i want just 03/11/2004. further problem is even though i managed to store just the date like 03/11/2004 in the database, whole date and time shows up when i display it in my pages.
any help will be appreciated.
thanx,
View 4 Replies
View Related
Apr 11, 2006
Hi!
Can anyone tell me how to perform a substring operation on a DateTime field. I'm using SQLServer. I have read that you may have to use casts but I have thus far been unable to get that to work.
My statement:
SELECT EventDate FROM tblEvent WHERE Category = 'GE';
I just want to cut the hours, minutes and seconds off, so that I am left with just the date element.
Thanks all.
Gren
View 1 Replies
View Related
