I'm trying to save times in datetime fields, but seem to have lots of issues with formats.
I'm using a multiline text box, where the user will pick from 09:00 09:30 10:00 etc, so the value that comes out in the .text property is "09:30"
Once I have that, whats the best way to convert this into the right format for a dt field? I understand it'll have todays date in it which is fine. It seems to work ok if I overwrite a textbox that's bound to a datetime field, but not if I'm sending it via an insert statement.
Also I'm using 24 hour format times, eg 14:00, do I need to do anything to ensure they're store in this format?
Is there a way to store a time in the SQLServer database, without the date? (e.g. just simply "HH:MM" instead of "HH:MM DD/MM/YYYY" which it stores it in)
I have 3 dropdownlist server controls on my webform for the dateofbirth field. One for the day("1", "2" format), one for the month ("Jan", "Feb" format) and one for the year("1994", "1995" format). When the submit button server control is clicked i need to concatenate the day, month and year in the format 01-jan-1994 since i have the dateofbirth field datatype as datetime in my sql server table. I have declared the date variable stored procedure as "@dateofbirth datetime". Provide me the full coding. Thanx in advance
Hello everyone I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output 25/05/2007 18:30 Does anyone know the best way to get this into the correct format for my SP parameter cmdPublish.Parameters.Add(new SqlParameter("@datPublishDate", System.Data.SqlDbType.DateTime)); cmdPublish.Parameters["@datPublishDate"].Value = ???? Thanks
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
Hello all,I'm kind of new to ASP.NET and I've hit my first unsolvable roadblock. I'm trying to create a formview insertcommand that allows me to place the current date into a DateTime field but I continuously get an Input string was not in a correct format error. I may be using the wrong functions, I'm unsure. I've messed with DateTime.Now.ToString() as well as GETDATE() but I have no idea how to get it to work. I've also tried to switch the variable type of the field to a String type as well as DateTime. I am running visual basic .net with Microsoft SQL Server 2005. Here is my code right now: Experiencing problems with technology? Just fill out the form below and your problem will be forwarded to the Information Technology department.</em></strong><br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TechticketdbConnectionString %>" SelectCommand="SELECT [CRDL#] AS column1, [Employee ID] AS Employee_ID, [Problem description] AS Problem_description, [Date submitted] AS Date_submitted, [Ticket number] AS Ticket_number FROM [Tickets]" DeleteCommand="DELETE FROM [Tickets] WHERE [Ticket number] = @Ticket_number" InsertCommand="INSERT INTO [Tickets] ([CRDL#], [Employee ID], [Problem description], [Date submitted]) VALUES (@column1, @Employee_ID, @Problem_description, GETDATE() )" UpdateCommand="UPDATE [Tickets] SET [CRDL#] = @column1, [Employee ID] = @Employee_ID, [Problem description] = @Problem_description, [Date submitted] = @Date_submitted WHERE [Ticket number] = @Ticket_number"> <DeleteParameters> <asp:Parameter Name="Ticket_number" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="column1" Type="Int32" /> <asp:Parameter Name="Employee_ID" Type="Int32" /> <asp:Parameter Name="Problem_description" Type="String" /> <asp:Parameter Name="Date_submitted" Type="String" /> <asp:Parameter Name="Ticket_number" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="column1" Type="Int32" /> <asp:Parameter Name="Employee_ID" Type="Int32" /> <asp:Parameter Name="Problem_description" Type="String" /> <asp:Parameter Name="Date_submitted" Type="String" /> </InsertParameters> </asp:SqlDataSource> I've left out the EditItemTemplate because it isn't going to be used for this form. Here is the InsertItemTemplate: <InsertItemTemplate> column1: <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'> </asp:TextBox><br /> Employee_ID: <asp:TextBox ID="Employee_IDTextBox" runat="server" Text='<%# Bind("Employee_ID") %>'> </asp:TextBox><br /> Problem_description: <asp:TextBox ID="Problem_descriptionTextBox" runat="server" Text='<%# Bind("Problem_description") %>'> </asp:TextBox><br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert"> </asp:LinkButton> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"> </asp:LinkButton> </InsertItemTemplate> <ItemTemplate> column1: <asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>'></asp:Label><br /> Employee_ID: <asp:Label ID="Employee_IDLabel" runat="server" Text='<%# Bind("Employee_ID") %>'> </asp:Label><br /> Problem_description: <asp:Label ID="Problem_descriptionLabel" runat="server" Text='<%# Bind("Problem_description") %>'> </asp:Label><br /> Date_submitted: <asp:Label ID="Date_submittedLabel" runat="server" Text='<%# Bind("Date_submitted") %>'> </asp:Label><br /> Ticket_number: <asp:Label ID="Ticket_numberLabel" runat="server" Text='<%# Eval("Ticket_number") %>'> </asp:Label><br /> <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"> </asp:LinkButton> <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"> </asp:LinkButton> <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New"> </asp:LinkButton> </ItemTemplate> </asp:FormView> I would like to get the value formatted as a DateTime but a String will do just fine if a DateTime variable won't work. Any help would be greatly appreciated. Thanks!
I have a calendar control on a page that is saving the date to a SQL Server 2005 table and this works fine; but how do I also save it with the current time? It is always saving as 12:00:00 AM. Thanks for the help.
I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE] @QuePoolID int=null ,@ApptTime time(7) ,@SendOnDate datetime
I thought I would post this just in case you are not aware of the change of the Daylight Saving Time start and end dates in 2007. This is really an OS problem, not a SQL Server problem, but it could have a big impact on your applicaitons if you are not prepared. The first change is less that 2 months away, so it's not a bad time to give it some thought.
Here are some articles about preparing systems for the 2007 daylight savings time changes that you might find of interest.
Preparing for daylight saving time changes in 2007: “Starting in the spring of 2007, daylight saving time (DST) start and end dates for the United States will transition to comply with the Energy Policy Act of 2005. DST dates in the United States will start three weeks earlier (2:00 A.M. on the second Sunday in March) and will end one week later (2:00 A.M. on the first Sunday in November).
Microsoft will be producing an update for Microsoft products affected by the new United States daylight saving time transition dates. These updates will be released through a combination of channels including Microsoft Customer Support Services (CSS), hotfixes incorporated in Knowledge Base articles, Windows Update, Microsoft Update, Windows Server Update Services (WSUS), and the Microsoft Download Center.� http://www.microsoft.com/windows/timezone/dst2007.mspx
How to configure daylight saving time for the United States in 2007: http://support.microsoft.com/?kbid=914387
Daylight Saving Time in Canada: The article in the link below indicates that all Canadian provinces except for Nunavut have decided to follow the US daylight savings changes in 2007. The article contains links to news articles with official announcements of the changes. http://en.wikipedia.org/wiki/Time_in_Canada
Hi, I have this simple SQL query which SELECTs the fields according to the criteria in WHERE clause. SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber, dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect, dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect, CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration FROM Calls WHERE (callingPartyNumber = ISNULL(@callingPartyNumber, callingPartyNumber)) AND (originalCalledPartyNumber = ISNULL(@originalCalledPartyNumber, originalCalledPartyNumber)) AND (finalCalledPartyNumber = ISNULL(@finalCalledPartyNumber, finalCalledPartyNumber)) AND (duration >= @theDuration) AND ((datetimeConnect - 14400) >= ISNULL(convert(bigint, datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), datetimeConnect)) AND ((dateTimeDisconnect - 14400) <= ISNULL(convert(bigint, datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), dateTimeDisconnect)) If you notice, in the SELECT, I add 3600 (1 hour) to adjust the time according to the current DayLight saving setting. Similarly, while comparing in WHERE clause, I subtract 14400 seconds (4 hours) to adjust the comparison according to EST (-5 GMT, but doing -4 GMT since daylight saving) and daylight saving. Also, dateTimeConnect and dataTimeDisconnect fields save time in UTC seconds. Now this application may be used by clients in different timezones. The application may be hosted on their server which would mean a different time zone. What would be the best way to make this time zone and daylight adjustment more dynamic instead of hardcoding the values.
The following T-SQL code is run in a vb.net (2003) module.
I am acquiring data from an OPC server into an array of data type object. It is necessary to declare the array as an object for the OPC server to return data. The OPC returns the data in 15 mS.
I now need to save this data to a table in SQL 2005 running on a 2003 server.
The table for saving the data has already been created and saving the data is actually an 'update ... set ... where' statement.
The statement is For i as short = 1 to itemCount
sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = " & itemValue(i) & " where TagName =' " & tagName(i) sql_command.executeNonQuery Next
The TagValue field datatype is decimal(18,6) The itemValue(i) datatype is object / variant. The itemValue array contains 95% values of type Single and the rest are Integers. The TagName datatype is varchar(50) The tagName(i) datatype is string
When I run the loop (255 iterations) with the above query it takes 1500 mS.
If I force a conversion by changing the query to sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = Convert(decimal, " & itemValue(i) & ") where TagName =' " & tagName(i) it takes 900 mS to execute for 255 iterations.
I tried one more variation sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = Convert(decimal(18,6), " & itemValue(i) & ") where TagName =' " & tagName(i) which takes 1200 mS to execute for 255 iterations.
If I use the following code dim tempValue as decimal = 123456789012.123456D sql_command = "Update [IO Log].[dbo].[IO Log] Set TagValue = " & tempValue & " where TagName =' " & tagName(i) it excutes in under 100 mS for 255 iterations.
There seems to be a problem during implicit / explicit conversion of type object/variant to decimal. I need to save the data received from the OPC (255 tags) in under 150 mS.
Hi,I have a table which I need to store times in. What is the best way of doing this? The only way I can think of so far is to save the times as a datetime such as '1/1/1900 03:00 PM' then format the date as "hh:mm tt" so that the day isn't displayed. Is this the best method?Thanks,Curt.
hi, i would like to store just the time part in sqlserver 2000. time part with default date which is 1899 - 12- 30 in my sql server. but i read some articles which mentioned default date as 1900 - 01 - 01. i am not sure which one but if i enter just the time part in my sql server and do the query i get 1899 -12-30 and (time part). anyway, i am just wondering whether it is possible to set the trigger or constraints to strip off the date part of entered value and replacing it with default is ok.
the reason is i need to some queries on time part only which is giving some problem now.
HI there,I currently store the date using the getdate() functionbut how can I store just the time or seperate the time off from adatetime datatype?M3ckon*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
Last weekend many of our severs had a failed job "collection_set_3_upload". The error that occured is: "Violation of PRIMARY KEY constraint 'PK_ active_ sessions_ and_requests'. Cannot insert duplicate key in object 'snapshots.active_sessions_and_requests'. The duplicate key value is (2824333, 2015-10-25 02:54:49.7630000 +02:00, 1)."Last weekend we happened to go from summer time to winter time. i.e. the clock passed 02:00 - 3:00 two times during this night.
I.e. there is a bug in the Data Collector component that collects data for the Management Data Warehouse: it uses local time instead of UTC. I've created a Connect item to report it to Microsoft.URL...how do you get your process running again? the job will no longer run because it will every 5 minutes keep on trying to upload the conflicting data for the 2nd 2:00 - 3:00 period. I've only found one solution: get rid of all data collected but not yet uploaded.
You do this by stopping the Collection set (in SSMS go to Object Explorer -> <the server you want to fix> -> Management -> Data Collection -> System Data Collection Sets. Right click "Query Statistics" and select "Stop Data Collection Set").Then you delete the cached results from the sql server machine's harddisk. These cached results are in files located in a Temp folder on the sql machine itself, inside the AppData folder for the service account SQL Server Agent is running under. Usually it will be something like: c:Users<sql agent service account>AppDataLocalTemp.
Inside this folder delete all files that have 'QueryActivity' in their name. You'll loose all data collected since the start of wintertime, but at least your data collection process will work again.After this you can start the Collection set again by right clicking it and select "Start Data Collection Set". Every 5 minutes the data will be summarised and uploaded into your management data warehouse.
Posting Data Etiquette - Jeff Moden Posting Performance Based Questions - Gail Shaw Hidden RBAR - Jeff Moden Cross Tabs and Pivots - Jeff Moden Catch-all queries - Gail Shaw
I'm wondering what is the best way to store time-series data in an SQL database?? I've done a bit of investigating on the rrdtool (round robin database tool) that is used in a lot of nix based solutions for monitoring network equipment. I have a need to collect performance data from servers and routers and then produces some nice graphs from that data. I'm just not sure who i should store that data without the database growing to some huge size.
I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
Sorry if this has been asked befor, but I couldn't find anything that hepled me.
I have a datetime field and want to extract only the time. I know it works with CONVERT and style-type 108. But I get something like this: 08:00:00 and I need something like this: 08:00.
Is here an easy way to get 9:00 PM from 5/1/2005 9:00:00 PM withoutdoing:Cast(DATEPART(HOUR,xDate) as varchar(2)) + ':' +Cast(DATEPART(MINUTE,xDate) as varchar(2))lq
I am trying to automate a basic task using SQL Server 2005 Express.
Currently I have a query script that I run and then save the results as a CSV file. I need to do this on a daily basis and so I am looking to find out how best to go about this. There are a multitude of third party tools that claim to be able to do this - can anyone recommend this or enlighten me of the best way to set up this automation.
Hi all, I am generating a report dynamically using ReportViewer control. I use a SELECT query to fill the dataset which in turn is used by the ReportViewer to display records. One of the fields is [End Date] and is a DateTime in SQL Server. When the ReportViewer is populated, the End Date field shows the time also with the date. How can I remove that time part? I have used these methods and I want to keep the value as DateTime only and not convert to a string or something. These don't work for me: CAST(FLOOR(CAST(m.[End Date] AS float)) AS datetime)
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
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
