Problem Trying To Trunc Datetime Field
Jan 23, 2008
Hi all,
I've just started using SQL Server and I'm having problems trying to trunc dates. In my application I have a table with information about calls and I need to group them by hour, day, month and year, and I would like to do it exactly in the same way that PostgreSQL or Oracle do, for example:
trunc(to_date('22-AUG-03'), 'YEAR') would return '01-JAN-03'
trunc(to_date('22-AUG-03'), 'MONTH') would return '01-AUG-03'
I need to store that grouped information in another table, so I'm trying to get for example:
22-AUG-03 01:00:00,
22-AUG-03 02:00:00 for hours,
22-AUG-03 00:00:00
23-AUG-03 00:00:00 for days and so on...
I'm trying to use convert function but I cant get the information in the way I need it.
Any kind of help will be apreciated. Thank you very much in advance.
View 5 Replies
ADVERTISEMENT
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
May 22, 2007
ok i thought trunc meant that it truncated the characters or data in a cell to something, but what is going on here?
UPDATE primary_mst set box_name_change = TRUNC(box_name_change);
site: http://www.deweydesigns.com
blog: http://www.deweydesigns.com/blog
myspace:
http://www.myspace.com/esotericstigma
View 4 Replies
View Related
Jan 9, 2007
First time poster...
Alright this is what I got...I am running MSSQL server 2000.
I have already restored a copy of the production db to our test sql server.
What I need to do is run some update scripts (from 3rd party vendor, going to newer version of their product). The database is about 60Gb and I filled up the transaction log (causing later updates to fail) while running one of their scripts that executed 194 routines of:
alter table Table_Name add New_Column int
GO
EXEC sp_bindefault mg_zero_dflt, [Table_Name.New_Column]
GO
update Table_Name set New_Column=1
GO
Will "sp_dboption 'dbname' 'trunc. log on chkpt' 'TRUE' " automatically truncate the transaction log when approaching the transaction log file limit while running these update scripts. Then when the updates are complete go ahead and "sp_dboption 'dbname' 'trunc. log on chkpt' 'FALSE' "
I'm not worried about logging the transactions during this process, I just don't want to have the transaction log fill up while running these scripts causing it to error out.
Or is there a way to make those changes using bcp?
View 3 Replies
View Related
Feb 18, 2004
HI ALL,
Please show me how to set the trunc. log on chkpt. database option to TRUE .
thanks so much
View 1 Replies
View Related
Aug 1, 2006
Hi....
I am new to SQL Server. I primarily use Oracle which allows me to trunc(date) to removed the timestamp from a date.
Can anyone tell me the equivalent in SQL Server?
Thanks!
Christine
View 3 Replies
View Related
Dec 27, 1999
I got the following message
"DUMP TRANsaction is not allowed while the trunc. log on chkpt. option is enabled: use DUMP DATABASE, or disable the option with sp_dboption. (Message 4208)"
on the scheduled task as follow
DUMP TRANSACTION TASS_DATA TO tass_log_dump WITH NOUNLOAD , STATS = 10, INIT , NOSKIP
Please help me ASAP!
Many Thanks!
View 1 Replies
View Related
Mar 17, 2006
hi
I just created a DB , & ran sp_dboption , it showed me that the trunc. log on chkpt. is true .....
What if i set the recovery model of this DB to full , would I be able to recover the DB to a specific point in time ,
or since trunc. log on chkpt. is true.. I would not be able to collec the transaction log backups ???
Cant understand this concept , need some link to read this stuff ....
Thanks
View 1 Replies
View Related
Oct 24, 2007
How to trunc (truncate) a table using enterprise manager?.
no trunc like toad in enterprise manager?.....
View 3 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
Jan 8, 2003
Can someone confirm that setting a db to use the simple recovery model in sql server 2000 is the same as setting a db to user trunc. log on checkpt. and select into/bulk copy on sql server 7?
View 3 Replies
View Related
Sep 29, 2006
hi,
how can I trunc an shring a db transaction log?
thanks for reply....
View 3 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
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
Jun 13, 2002
I am selecting a datetime value from a table but I want it to be
in this format: mm/dd/yyyy
This query is as close as I have come:
select char(month(nancyDateSent))+ '/'+ datename(day,nancyDateSent) + '/' + datename(yy,nancyDateSent) from tbloutgoingobms_hold
but is not correct - I get dates that look like this: /11/2002
Please help - there must be an easier way !!!
Thanks in advance,
Nancy
View 1 Replies
View Related
Aug 8, 2000
Hello, I would appreciate any suggestions
I've got a datetime field that I'd like to store as just the date without the time component, but still to keep it defined as a datetime field. I ran this update statement but this conversion isn't working. Conversion to char gives me what I want but I need to keep the field as a date datatype if possible.
Thanks :)
update
<table>
set
<column>=convert(datetime,convert(char(10),hire_date,101)) )
View 3 Replies
View Related
Oct 13, 1999
Hi,
I have a table with datetime field in SQL server 7.0
I have inserted values into datetime field
for eg: '10/10/99'
'10/10/99 10:30 AM'
'11/10/99 11:50 PM'
When I view this table from enterprise manager, values are shown exactly
in the same format I have entered.
But, When I run the query from Query Analyzer, It always shows values in the following format '1999-10-10 00:00:00.000'
Is there a way that I can see the values in the same format I have entered.
Help !!!
Thanks
View 1 Replies
View Related
Jan 31, 2003
I've got a table with the following fields and data types
ProjIDint
StatusDatedatetime
ProjStatusvarchar(1)
I want to put a unique index on the fields above, each ProjID can only have one ProjStatus on a given StatusDate (dd/mm/yyyy) , but because of the milliseconds in the date time it's not happening. Any suggestions on how I can get around this.
Thanks in advance.
View 1 Replies
View Related
Jul 6, 2004
Hello,
Can I simply set the default value of a dateTime field with the date and time that the record was created?
View 2 Replies
View Related
