SQL Substring On DateTime Field
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Create Date Field From Substring Of Text Field
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View Replies !
View Related
Substring'ing A Number-field
Hi :) and thanx for reading my post.I have to create a view based on a character in a number-field (fnr)which is 11 char's long. I have to get character nr "7".The problem is that i cannot use :--> SELECT fnr FROM table WHERE substring(fnr,7,1)since it's not a string.Any other way i can solve this ? Would be greatly and insanely happy ifanyone could help me out with a sample code or something.!Best regardsMirador*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
View Related
Substring On Text Field
Hi, According to the docs, when using substring on a text field (not varchar): substring (<text>, start, length) ...the length in this case, represents BYTEs as opposed to number of characters. So my question is, how many bytes per character (or characters per byte)....or is this a possible conversion? thanks,
View Replies !
View Related
Extracting Substring Into New Field
I am trying to populate two fields in my table with the first 6 characters in an existing field with the following command: SELECT dimension_elist, SUBSTRING(dimension_elist FROM 1 FOR 6) AS BU_Number SUBSTRING(dimension_jde_pl FROM 1 FOR 6) AS Acct_Number FROM JDE_Import The result is an error: Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'FROM'. I am fairly new to SQL so I am probably missing something basic. Does anyone have any thoughts?
View Replies !
View Related
DateTime Unable To Save In Datetime Field Of SQL Database
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 Replies !
View Related
Informix Date Type Field To SQL Server Datetime Field Error
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 Replies !
View Related
Datetime Field
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 Replies !
View Related
DateTime Field
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 Replies !
View Related
Hlp With Datetime Field
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 Replies !
View Related
DateTime Field Nightmare!!
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 Replies !
View Related
Get The Time From A DateTime Field
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 Replies !
View Related
How To Get Time Value From Datetime Field?
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 Replies !
View Related
Help Inserting Into Datetime Field
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 Replies !
View Related
SQL DateTime Field Query
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 Replies !
View Related
Getting Just The Date Or From DateTime Field
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 Replies !
View Related
Date From Datetime Field
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 Replies !
View Related
Datetime Field Overflow
Using AccessXP as front-end to sql2000 backend. Have .net pages adding/updating/changing fine. But, on Access form when adding a new record, I get: ODBC call failed. Microsoft ODBC SQL Driver. Fractional truncation (#0) DateTime field overflow (#0) But, if I'm updating a record that's already been entered and just change 3/15/2006 06:30:00 pm to 3/15/2006 06:45:00 pm there's no error. Any Help? Thanks, Janet
View Replies !
View Related
Index On Datetime Field
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 Replies !
View Related
Null Datetime Field
Hi, I am trying to load data into a smalldatetime field from a flatfile. The data that looks like this '20001201' I have no problem converting to a smalldatetime field. However, sometimes there is no date, the date looks like this '00000000'. When I try and convert this to a smalldatetime field I get an out of range error message. Is there any way to have a date field that looks like this 0000-00-00?
View Replies !
View Related
Datetime Field Conversion
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 Replies !
View Related
INSERT INTO DateTime Field
Which of the following syntax's is correct to insert datetime data from a text file into a datetime (or smalldatetime) field(My_date) in SQL7?: INSERT INTO tblDateTest (My_Date) VALUES('12/12/9998') or INSERT INTO tblDateTest (My_Date) VALUES(12/01/9998) (results in 1900-01-01 00:00:00.000 being stored) My question revolves around what impact the single quotes (or lack of) has on the command. Is it required? Does it tell SQL I'm inserting a string that has to be implicitly converted by SQL into datetime? I've visited Books Online and still a little foggy on this. Randy
View Replies !
View Related
Formatting A Datetime Field
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 Replies !
View Related
Datetime Field Format
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 Replies !
View Related
Select Datetime Field With Vb.net
Hello to all. I try to get record count on a sql 2005 table between two date. My Sql string is Select * from TableName where data between '2008-03-01' and '2008-03-31' but vb returna conversion error : the conversion fromchar data type to a datetime have created a datetime value not valid. someone can help me ??? thank you in advance !!!! Alberto
View Replies !
View Related
Convert DateTime Field
Hi, I'm not entirely sure that this is the correct forum for this question but it relates to my SSIS package. I am currently implementing an SSIS package to replace an existing stored procedure which is getting very unmanageable. I have come across a part in the stored procedure which performs a convert(datetime, @Parameter3, 14) on a string of data. The string value of @Parameter3 is in the following format HH:mm:ss. The problem i am having is how to implement similar functionality in a script task. everything i have tried involving the datetime object returns 1,1,0001, 00:00:00 or similar. It never seems to get the time so that it can be passed to a datetime field in the database. Thanks in advance for any help that is provided. Grant
View Replies !
View Related
Querying DateTime Field
Hi all, 1. I need to query a DateTime field, I'm using the following which works but I was just wondering if there is a faster or better way to query a DateTime field, omitting the Time part: CAST(FLOOR(CAST(dbo.tblContactLog.DateCreated AS FLOAT)) AS DateTime) <=' + '''' + @EndDate + '''' 2. Also, I'm using the getdate() function to insert the date the record was entered, this always puts in the Date and the Time, is there a way to just put in the Date part? Thanks
View Replies !
View Related
Get Date Only From Datetime Field!
OK, I know this is really simple but I can't get my statement to pull just the date from a datetime field! Here's my query: select * from tblPR where date between convert(datetime, dateadd(day,-day(getdate())+1,getdate()),103) and convert(datetime, dateadd(day,-day(getdate()),dateadd(month,1,getdate())),103) I get no errors but I get the timestamp too and I only want the date. Where am I going wrong?! Thanks in advance, Brett
View Replies !
View Related
Update Datetime Field??
hello friend !! i want to update date field but i am getting error like update emp set convert(varchar(50),date_t,121) = '2006-03-31 19:56:36.933' Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'convert'. please help me out T.I.A Shashank
View Replies !
View Related
Datetime Field - SQL's Problem Or C#?
Hello I have a column called *dDateTime* I insert data in to this column from my C# code. For example I pass in the value: 2008/03/30 10:27:49.183 However, when I open the SQL Server table it will display the value as : 3/30/2008 10:27:49 AM I’m using the convert.todatatime(X) in C# - and it looks like it changes the data to 3/30/2008 10:27:49 AM SQL Server itself puts some kind of ms behind it – doesn’t seem to be related at all :S (but it doesn't use the ms in the uniqueness of the column, it stops at seconds) Now here is the kicker. The dDateTime is a unique field, thus when I insert another record with let’s say 2008/03/30 10:27:49.356 IT SHOULD BE OK!!!!! HOWEVER, it will throw an error: Cannot insert duplicate key in object 'X'. – it ignores the milliseconds when enforcing the uniqueness. Once the data hits the next second (2008/03/30 10:27:50.632), it will insert fine again, but will miss all data in between. Is there a setup somewhere that I’m missing? Thanks in advance!
View Replies !
View Related
Select All Dates From SQL Datetime Field
I have a form that's filtering search results and porting to a gridview. The drop down is selecting from the date column of SQL, but i want it the default value to show all dates in the gridview. In a normal string field, you can just use "%", but a datetime field won't take this. What can i use to show all dates?
View Replies !
View Related
Problem Querying On DateTime Field
Hello all, I am having an issue querying DateTime. The field I am querying on holds a full datetime stamp, such as "29/03/2008 13:30:00". I need to query the Date only, and return the results that match the specified date regardless of the time stamp. So my query would look like this:- SELECT * FROM table WHERE Date = '29/03/2008' At the minute my code returns nothing. Is there way way to query the Date only ?? Thanks,Sean
View Replies !
View Related
SQL Datetime Field Type Command Help??
I am new to asp.net, C#, and sql server, coming from php and ODBC/Access background (trying to leave all that behind) Anyway, I have a textbox that will be used to INSERT a date into a SQL datetime field. The date coming from the textbox will be formatted like "MM/dd/yyyy". For my purposes the day, month, and year are all that I require, the time is irrelevant. Then from a different page in the site I will be using a variable containing the current date derived from "DateTime.Now.ToString("MM/dd/yyyy")" to select records with a matching date. Is there anyone that has an example showing both inserting into a datetime field and extracting records based off the date format that I have specified? Would it be better to use a different format? In the past I have stored dates as a julian in an int field type but I would rather learn to use the datetime field type in sql. Any help would be greatly appreciated.
View Replies !
View Related
How To Compare Time......... Using DateTime Field
hi guyz i want to compare time from DateTime field i.e. i want to identify if the time is from 1pm to 2pm the do this else do...... select DATEPART(hour, loginTime) ......returns me the hour i can get the Hour part of the time but the prblem is how to identify it whether it is less than 2:00:00 pm and greater than 1:00:00 pm i can do this task using at application level but i want this to b done at query level any ideas??????????
View Replies !
View Related
Inserting NULL Into Datetime Field
I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand. The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value�), so I don’t know how I can insert this value when the field is blank? Can anyone shed some light please? Thanks
View Replies !
View Related
Cant Insert NULL To DateTime Field
Hi I'm using DetailView and I have a text box which show the date. I have formated the date as shortDate {0:d} format. I want to insert/update null if that text box is empty. I have tried putting null value in my Update statement in sqlDataSource. And I'm getting error saying can't convert varchar to smalldatetime. If I don't set null value as above, some large date (01/01/2033) has been inserted in my database. Can anyone help me? Moe
View Replies !
View Related
How To Retrive Date(alone) From Datetime Field
hai In my web application i want to bind data from sql 2005 to ultrawebgrid, when i use source code dim cmdselect as sqlcommand dim cmdstring as string cmdstring="select name, datefieldname from tablename" cmdselect=new sqlcommand(cmdstring,connectionstring) connectionstring.open() ultrawebgrid1.datasource=cmdselect.executenonquery() ultrawebgrid1.databind() connectionstring.close() ----- when i execute above coding i am geting date and time displayed there, but i want to display date alone --my datefield datatype is datetime - i am using sql 2005,(vs2005-vb/asp.net) please help me thanks in advance
View Replies !
View Related
Problem Inserting Now() Into A Datetime Field
HiI am trying to insert value retrieved from Now() into a datetime field in my MSDE database, but I am getting the following error, and I have no idea what is going wrong.Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Here is the code I am using:Dim user As String = MyContext.User.Identity.Name.ToString Dim TimeDate As DateTime = Now() Dim status As String = "Pending" With SqlOrders.InsertParameters .Item("UserName").DefaultValue = user .Item("OrderDate").DefaultValue = TimeDate .Item("Status").DefaultValue = status End With SqlOrders.Insert()The date is being returned in this format 23/03/2006 02:01:52, which is the same format as it should appear in the database. could anyone please tell me where I am going wrong?
View Replies !
View Related
About The Type Field : Datetime And Smalldatetime
hi all, the field type :datetime and smalldatetime, i still can't understand.everytime when i inserted the data to the db, i also get the error message "System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."i must change the field type to string, so that i can insert data my codetxt_datetime.text = '5/2/2006'insert into datetime (datetime) values ('"& txt_datetime.text & "')"can anybody tell me the reason???thank you!!
View Replies !
View Related
Error Updating A DateTime Field
Hi, I'm having trouble updating a DateTime field in my SQL database. Here is what I'm trying to do....I retrieve the existing value in the DateTime field (usually a bum date like 1/1/1900 00:00:00:00), then put it in a variable. Later, depending on some conditions, I'll either update the DateTime field to today's date (which works great) or set it back equal to the existing value from the variable (this one messes up and says "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. "). There is a ton more than this but here are the relevant snippets:<code>Dim CompDate As DateTimeDim aComm As SQLCommand Dim aReader As SQLDataReader Dim bSQL,bConn As String bSQL= "SELECT CompleteDate,StatusOfMarkout FROM Tickets WHERE TicketName=" _ & CHR(39) & Trim(Ticket.Text) & CHR(39) bConn = serverStuff aConn = New SQLConnection(bConn) aComm = New SQLCommand(bSQL,aConn) aConn.Open() result = aComm.ExecuteReader() 'fills controls with data While result.Read() CompDate = result("CompleteDate") PreviousMarkoutStatus.Text = result("StatusOfMarkout") End While result.Close() aConn.Close()sSqlCmd ="Update OneCallTickets CompleteDate=@CompleteDate, StatusOfMarkout=@StatusOfMarkout WHERE TicketFileName=@TicketFileName" dim SqlCon as New SqlConnection(serverStuff) dim SqlCmd as new SqlCommand(sSqlCmd, SqlCon) If Flag1List.SelectedItem.Value = "No Change" Then SqlCmd.Parameters.Add(new SqlParameter("@Flag1", SqlDbType.NVarChar,35)) SqlCmd.Parameters("@Flag1").Value = PreviousMarkoutStatus.Text SqlCmd.Parameters.Add(new SqlParameter("@CompleteDate", SqlDbType.DateTime, 8)) SqlCmd.Parameters("@CompleteDate").Value = CompDateElse SqlCmd.Parameters.Add(new SqlParameter("@Flag1", SqlDbType.NVarChar,35)) SqlCmd.Parameters("@Flag1").Value = CurrentStatus.Text SqlCmd.Parameters.Add(new SqlParameter("@CompleteDate", SqlDbType.DateTime, 8)) SqlCmd.Parameters("@CompleteDate").Value = Today()End IfSqlCon.Open() SqlCmd.ExecuteNonQuery() SqlCon.Close()</code>Can anybody help me with this? Thanks a bunch
View Replies !
View Related
Insert TIME Only In DateTime Field
I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?
View Replies !
View Related
Default Field Value For DateTime && SmallDateTime
In SQL Server 2000 / Asp.Net I am trying to use default values for all fields; hoping to eliminate nulls. For number and character fields, the default is pretty obvious, but is there any empty value for a date field? I think a null there might be better than putting in a bogus date, at least it can be tested for. Are there any more developend ideas on this question? Many thanks Mike Thomas
View Replies !
View Related
Extracting Time From The SQL DateTime Field.
Any help on extracting the time part from the datetime field in SQLdatabase.even though i had entered only the time part in the database when iextract the field it gives me only the date part. i’m using Vb.netdatagrid as a front end.any assistance appreciated!! :?:--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254266.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877989
View Replies !
View Related
Searching A Datetime Field By Time
I have the time of an event stored on each record as a datetime field.Itincludes the year,month,day, etc. Suppose my user wants to search the tablefor all events over the lunch hour, say between11am and 1pm. How do Iconstruct the SELECT query to peek into each datetime field and return onlythose records that satify the specified time range?Many thanks.
View Replies !
View Related
Automatic Updating Of Datetime Field
I need to automatically update a datetime field for a record to the current time whenever the record is updated. create table t ( id bigint identity(1,1) not null primary key, name varchar(50), value varchar(50), ts datetime not null default getutcdate() ) go insert t (name, value) values ('fred', 'bob') go update t set value='robert' where id=1 and name='fred' go One option would be to use an instead of update trigger. create trigger update_t on t instead of update as update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id go update t set value='dick' where id=1 and name='fred' go Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do. Any ideas?
View Replies !
View Related
Datetime Field Not Population Properly
hi all, Pls. help me out in solving this issue. im having a table in sqlserver storing datetime. some sample values in the field are, 2004-12-01 11:05:33.530 2004-12-01 11:05:33.920 2004-12-01 11:05:34.590 2004-12-01 11:05:34.590 i have a perl script doing a simple select statement to retrive the value the values i get are like this Dec 01 2004 11:05AM Dec 01 2004 11:05AM Dec 01 2004 11:05AM Dec 01 2004 11:05AM i can not figure out the problem. i havent formated it any where in my code. when i execute the same code in query analyzer im geting the result properly. can any one help me out Note: im using freetds to fetch data from sqlserver through perl thanks in advance...
View Replies !
View Related
Select Only Date From Datetime Field
I know there must be a way to do this.... I want to select only the date portion of a datetime field. the date normally shows as similar to '2004-01-01 09:39:52.000' I need to return just '2004-01-01'. using convert and cast returns the dates as 'JAN 01 2004' - this won't sort correctly, so it is of no use. any suggestions? thanks, john
View Replies !
View Related
|
TRACKER
