Im storing in a table the value corresponding to average answer time of incoming calls of our call center. The time is kept in seconds, however I need to show this value in minutes not in seconds, if I divide the seconds by 60 , I don€™t get the correct time in minutes, for example:
Time in minutes : 00:02:16 (2 minutes and 16 seconds)
Time in seconds : 136 Seconds (that€™s the value stored on my table)
Time in seconds converted in minutes : 136 / 60 = 2,2666666 minutes
I want to get 2,16 minutes
Is there any way to do this conversion (any sql function)?, and also is it correct to do this conversion ?
I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.
I'm struggling with this for a quite a while and I'm not able to get it working.
I tried the oracle query something like this,
SELECT (TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE
FROM TBLA
this gives me an output of 20070511 23:06:30:000
the space in MM : SS is intentional here, since without that space it appread as smiley
I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error
The value could not be converted because of a potential loss of data
I'm struck with error for hours now. Any pointers would be helpful.
I can get my DB to accept my date by doing the following: row.Item("RequestDate") = Me.fullDate.Date -----I have fulldate dimensioned as date above. However if I try to do the follwing for a Time it gives me an error when it trys to update the DB the column is set to datetime & when I check the value of the row Item in my command window it says ?row.Item("BeginTime")#6:00:00 AM# {Date}[Date]: #6:00:00 AM# row.Item("BeginTime") = CDate(ddlBegin.SelectedValue & beginAMPM)row.Item("EndTime") = CDate(ddlEnd.SelectedValue & endAMPM)The SQL Error I get is the following: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. 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.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.Source Error:
Line 335: row.Item("EndTime") = CDate(ddlEnd.SelectedValue & endAMPM) Line 336: DsVacationData1.RequestData.AddRequestDataRow(row) Line 337: SqlDataAdapter2.Update(DsVacationData1) Line 338: DsVacationData1.AcceptChanges() Line 339: End SubThanks for any help.
I'm using SQL server and in the database the 'epDischargeTime' is stored as '51600' for example. How I make sense of this and convert it to something like 10:00am or something. I need to find the % of patients that were discharged before 10am. Thanks in advance!
I will try keeping this short. I am connecting to DBS on an AS400. One of the columns in the table in DB2 that I am trying to get is described as ISO Time. When I use AS400 operations navigator and run a SQL query on the column I can see the time in the correct format Eg: 12:27:26. So the source looks ok.
I then connect to DB2 in SSIS, using the ODBC driver etc etc. I have a data reader source that then connects to the DB2 table, using a SQL command and gets the data. Ideally this would then go straight into a OLE DB Destination to get to my SQL Server. I have put a data viewer between the Data Reader and the destination to see the actual values coming through, and the time column is now being displayed as 44846000000.
I have tried using a Data Conversion task, but no combinations seem to get the time back into the correct format.
I'm setting up a website for a new employer and their existing database. The table I'm using has a Time field in it that captures the date and time of the record. I'm trying to tie that field into some label controls and can't seem to figure out how to convert the time to just a date. I tried Format(TimeColumn, "MM/DD/YYYY") which does nothing but put my intended formatting as the label. What do I need to do to convert the time to just a date?
Does any of you have an SQL example that will convert a given date to the number of seconds since 1970? We have one that does the opposite (seconds to date), but can't figure out how to go date to seconds.
I am querying XML data, which the data and time is returning in this format:
2015-01-16T16:06:14.577-06:00
This is my query:
SELECT CONVERT(XML,BUSINESSOBJECTIMAGE).value('(NewDataSet/Table1/InstalledDate)[1]', 'nvarchar(100)') AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL
Running that, I get the native Date Time format, as I pointed out above. So, I've tried multiple ways to convert that into SQL format. I tried:
SELECT CONVERT(XML,BUSINESSOBJECTIMAGE).value('xs:dateTime((NewDataSet/Table1/InstalledDate)[1])', 'nvarchar(100)') AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL
This doesn't seem to make a difference and still gives me the date in native XML format as identified above. So, then I tried this:
SELECT CONVERT(XML,BUSINESSOBJECTIMAGE).value('xs:dateTime((NewDataSet/Table1/InstalledDate)[1])', 'datetime') AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL
Now I get an error:
Msg 242, Level 16, State 3, Line 1
The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.
So, then I try converting it:
SELECT CONVERT(datetime,CONVERT(XML,BUSINESSOBJECTIMAGE).value('xs:dateTime((NewDataSet/Table1/InstalledDate)[1])', 'nvarchar(100)')) AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL Then I get this error: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
So, then I try converting it with 127 datetime type and get the same error:
SELECT CONVERT(datetime,CONVERT(XML,BUSINESSOBJECTIMAGE).value('xs:dateTime((NewDataSet/Table1/InstalledDate)[1])', 'nvarchar(100)'),127) AS 'Installed Date' FROM CORE_AUDITINGTRAIL.AUDITDETAIL
Hi,ddl & dmlproject varchar(10) start char(5) stop char(5)------------------------- ----- -----hey now 21:00 19:25new test 20:25 20:30t 10 21:00 NULLt 11 21:10 21:35t 12 21:30 22:40t 12 7:05 11:10test me 08:00 14:25test me 17:00 17:55what I want is to calculate time duration using hour (h.1decimal) e.g.1.2 :what I have now using the following query:select project, start, stop,CASE WHEN (datediff(n,start,stop) < 0) THEN -1WHEN (datediff(n,start,stop) < 1) THEN (CAST(datediff(n,start,stop)as decimal(1)))ELSE Convert(decimal(1),(datediff(n,start,stop)/60)) END astotal_hoursfrom testTBlgroup by project, start, stopoutput:project start stop total_hours------------------------- ----- ----- -----------hey now 21:00 19:25 -1new test 20:25 20:30 0t 10 21:00 NULL NULLt 11 21:10 21:35 0t 12 21:30 22:40 1t 12 7:05 11:10 4test me 08:00 14:25 6test me 17:00 17:55 0If the calcuate is right I'd like to remove start and stop columns,so, it would just return project and the sum of hours including lessthan an hour in decimal for each.Thank you.
Can anyone tell me how to convert julian date time to DateTime and Vice Versa?the function which I have only convers the date to Julian and julian to date but the time is not appended. How can i get the time into Julian format and from julian format?Any help would be appreciated.thanks.
I''m working on a data conversion process and trying to convert data from SQL Server to Oracle 10g. The problem I'm encounterin g is that when I go from SQL to Oracle with a date into a Timestamp field in Oracle, it errors out. Not because it's only Timestamp, Oracle accepts dates in that field type. I don't know why this is happening, but I'm sure someone else has run into this...thoughts?
Well here is the problem iam trying to evaluate a expresion and return a string but when i run my code it always return where my expresion is false where it should return true. here is my code.
BEGIN DECLARE @datefin_flag char(13), @strip datetime select @strip = getdate() --select convert(char(10),@strip,120) select @datefin_flag = dateend FROM mattstest WHERE convert(char(10),datebegin,120) <= convert(char(10),@strip,120) and convert(char(10),dateend,120) >= convert(char(10),@strip,120) --select @datefin_flag --UPDATE dateflagevent SET flagevent = getdate() FROM dateflagevent IF (@datefin_flag = @strip) BEGIN print 'Run' END ELSE print 'You cant run this' END
I think that the problem is the date and time they are the same but not in the right format its like saying 2004-01-25 is equal to janv 25 2005 how do i correct this.
A common problem in moving data between SQL Server and UNIX systems is converting to/from the SQL Server datetime format to the UNIX time format.
There are several UNIX time formats, but the most common is a signed 32-bit integer that represents time as the number of seconds between 1970-01-01 00:00:00 and a given time. The functions in the script can be use to convert between SQL Server datetime and this UNIX time format.
For more information on UNIX Time, please read this link: http://en.wikipedia.org/wiki/Unix_time
For more information about SQL Server date/time conversions, refer to this link: Date/Time Info and Script Links http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
The conversion of UNIX Time to SQL Server datetime is fairly trivial using the SQL Server DATEADD function, and this is the logic used by the F_UNIX_TIME_TO_DATETIME function in the script:
declare @UNIX_TIME int select @UNIX_TIME = 1111111111 -- Using dateadd to add seconds to 1970-01-01 select [Datetime from UNIX Time] = dateadd(ss,@UNIX_TIME,'1970-01-01')
Results:
Datetime from UNIX Time ------------------------------------------------------ 2005-03-18 01:58:31.000
(1 row(s) affected)
The conversion of SQL Server datetime to UNIX Time is more complex. SQL Server datetime is accurate to milliseconds so is necessary to either truncate or round off the time to a whole second. The function in the F_DATETIME_TO_UNIX_TIME script rounds the time down if milliseconds is less than 500 and up otherwise. A second problem is that UNIX Time is an integer, so it can only represent time from 1901-12-13 20:45:52 through 2038-01-19 03:14:07. The range of SQL Server datetime is 1753-01-01 through 9999-12-31, so the function in the script has logic to return a NULL if the datetime is outside the valid UNIX Time range. Another minor issue is that the SQL Server DATEDIFF function will not cover the full range of an integer value with seconds, so it is necessary to have additional logic in the function do cover the time from 1901-12-13 20:45:52 to 1901-12-14 00:00:00.
The function names created by this script are: dbo.F_DATETIME_TO_UNIX_TIME( @DAY ) dbo.F_UNIX_TIME_TO_DATETIME( @UNIX_TIME )
The script also includes code to test and demo the functions.
if objectproperty(object_id('dbo.F_DATETIME_TO_UNIX_TIME'),'IsScalarFunction') = 1 begin drop function dbo.F_DATETIME_TO_UNIX_TIME end go create function dbo.F_DATETIME_TO_UNIX_TIME ( @DAY datetime ) returns int as /* Function: F_DATETIME_TO_UNIX_TIME
Finds UNIX time as the difference in seconds between 1970-01-01 00:00:00 and input parameter @DAY after rounding @DAY to the neareast whoie second.
Valid datetime range is 1901-12-13 20:45:51.500 through 2038-01-19 03:14:07.497. This range is limited to the smallest through the largest possible integer.
Datetimes outside this range will return null. */ begin declare @wkdt datetime
-- Return null if outside of valid UNIX Time range if @DAY < '1901-12-13 20:45:51.500' or @DAY > '2038-01-19 03:14:07.497' return null
-- Round off datetime to nearest whole second select @wkdt = dateadd(ms,round(datepart(ms,@DAY),-3)-datepart(ms,@DAY),@DAY)
-- If date GE 1901-12-14 if @wkdt >= 712return datediff(ss,25567,@wkdt)
-- Handles time GE '1901-12-13 20:45:52.000 and LT 1901-12-14 return -2147472000-datediff(ss,@wkdt,712)
end go if objectproperty(object_id('dbo.F_UNIX_TIME_TO_DATETIME'),'IsScalarFunction') = 1 begin drop function dbo.F_UNIX_TIME_TO_DATETIME end go create function dbo.F_UNIX_TIME_TO_DATETIME ( @UNIX_TIME int ) returns datetime as /* Function: F_UNIX_TIME_TO_DATETIME
Converts UNIX time represented as the difference in seconds between 1970-01-01 00:00:00 to a datetime.
Any valid integer -2,147,483,648 through 2,147,483,647 can be converted to datetime. */ begin
return dateadd(ss,@UNIX_TIME,25567)
end go
go /* Demo functions F_DATETIME_TO_UNIX_TIME and F_UNIX_TIME_TO_DATETIME by converting a datetime to UNIX time and back to datetime. */
select [Input Datetime] = convert(varchar(23),DT,121), [UNIX Time] = dbo. F_DATETIME_TO_UNIX_TIME(a.dt), [Datetime from UNIX Time] = -- Convert datetime to UNIX time an back to Datetime convert(varchar(23), dbo. F_UNIX_TIME_TO_DATETIME(dbo. F_DATETIME_TO_UNIX_TIME(a.dt)),121), Note = .a.note from ( selectDT = getdate(), Note = 'Current date' union all selectDT = dateadd(ms,500,getdate()), Note = 'Current date + 500 ms' union all selectDT = dateadd(ms,750,getdate()), Note = 'Current date + 750 ms' union all selectDT = '1901-12-13 20:45:51.500', Note = 'Earliest datetime function can convert' union all selectDT = '2038-01-19 03:14:07.497', Note = 'Last datetime function can convert' union all selectDT = '2001-09-09 01:46:40', Note ='UNIX time 1000000000' union all selectDT = '2005-03-18 01:58:31', Note = 'UNIX time 1111111111' union all selectDT = '2009-02-13 23:31:30', Note ='UNIX time 1234567890' union all selectDT = '1901-12-14 00:00:00.000', Note = 'Date time dateadd second limit' union all selectDT = '1901-12-13 23:59:59.000', Note = 'Date time dateadd outside second limit' union all selectDT = '1901-12-13 20:45:51.497', Note = 'Date time function cannot convert - low end' union all select DT = '2038-01-19 03:14:07.500', Note = 'Date time function cannot convert - high end' union all select DT = '1753-01-01 00:00:00.000', Note = 'Min Datetime' union all select DT = '9999-12-31 23:59:59.997', Note = 'Max Datetime' ) a
Results:
Input Datetime UNIX Time Datetime from UNIX Time Note ----------------------- ----------- ----------------------- -------------------------------------------- 2006-05-29 23:34:11.517 1148945652 2006-05-29 23:34:12.000 Current date 2006-05-29 23:34:12.017 1148945652 2006-05-29 23:34:12.000 Current date + 500 ms 2006-05-29 23:34:12.267 1148945652 2006-05-29 23:34:12.000 Current date + 750 ms 1901-12-13 20:45:51.500 -2147483648 1901-12-13 20:45:52.000 Earliest datetime function can convert 2038-01-19 03:14:07.497 2147483647 2038-01-19 03:14:07.000 Last datetime function can convert 2001-09-09 01:46:40.000 1000000000 2001-09-09 01:46:40.000 UNIX time 1000000000 2005-03-18 01:58:31.000 1111111111 2005-03-18 01:58:31.000 UNIX time 1111111111 2009-02-13 23:31:30.000 1234567890 2009-02-13 23:31:30.000 UNIX time 1234567890 1901-12-14 00:00:00.000 -2147472000 1901-12-14 00:00:00.000 Date time dateadd second limit 1901-12-13 23:59:59.000 -2147472001 1901-12-13 23:59:59.000 Date time dateadd outside second limit 1901-12-13 20:45:51.497 NULL NULL Date time function cannot convert - low end 2038-01-19 03:14:07.500 NULL NULL Date time function cannot convert - high end 1753-01-01 00:00:00.000 NULL NULL Min Datetime 9999-12-31 23:59:59.997 NULL NULL Max Datetime
(14 row(s) affected)
Edit: Fixed minor bug that caused an overflow, instead of returning NULL, if input to function F_DATETIME_TO_UNIX_TIME was >= 9999-12-31 23:59:59.500.
Hi,I've run into a date conversion problem.When my package starts, I use a SQL Execute task to insert a record into a table. I set the SQLStatementSource value as follows:
StartDateTime is a column of type datetime. The INSERT results in the error:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. SQL Server is treating this as the 2nd day of the 14th month. I don't necessarily have control over the language of the destination SQL Server. Ideally I would like to format this as '14 Feb 2006 ...' which is totally unambiguous. Can anyone suggest a way of doing that in the expression editor, or some other workround? Thanks - Jerzy
conversion failed when converting date and or time from character string
I am using Sql Server 2008.(database designed for sql 2005 later moved to sql server 2008).
Pickup_time and actual_Pickup_time are varchar(5) in database.
What is wrong with this query?
Query:
SELECT COUNT(Trip_ID) AS OntimePickupCount FROM MyTABLE WHERE Start_Dt BETWEEN '01/01/2014' AND '04/30/2014' AND (DateDiff(minute, CAST (Pickup_Time AS time), CAST (Actual_Pickup AS time )) BETWEEN 0 AND 15 OR DateDiff(minute, CAST (Actual_Pickup AS time), CAST (Pickup_Time AS time) ) BETWEEN 0 AND 15) AND Actual_Pickup IS NOT NULL AND Actual_Dropoff IS NOT NULL
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.
I am importing a csv file to SQL 2005 table. The source column is coming as datetime. The destination filed is a datetime type. I would like to update the destination with the time part from the source. I used the data conversion to convert it to time using "database time[DT_DBTIME]". For a source value "2/08/2007 21:51:07" this inserts a value "2007-08-03 21:51:07.000". I need the column to have a value as "1900-01-01 21:57:07.000".
Can someone please tell me how do I do this conversion?
This is my code and I don't know why this error keeps coming out : PS : I did cursor to execute query.Th error showed is bold:
DECLARE RegCreatedDate CURSOR FOR SELECT DISTINCT (CONVERT(NVARCHAR,CreatedDate,103))Â FROM CA_Registration WHERE Month(CreatedDate)= @paMonthIn AND YEAR(CreatedDate)=@paYearIn OPEN RegCreatedDate FETCH NEXT FROM RegCreatedDate INTO @RegCreatedDate WHILE @@FETCH_STATUS = 0
I'm trying to select only July from show_held but I keep on getting the error message saying:
Conversion failed when converting date and/or time from character string.
I get error message after I write this code:
ANDshow.show_held = '&July&'
As you can see from the below code, How do I select July from times_held_in_July?
SELECTevent_name, DATENAME (MONTH, show_held) AS times_held_in_July FROMevent, show WHEREevent.show_id = show.show_id
Result:
event_name times_held_in_July DressageJuly Jumping July Led in July Led in September Led in May DressageApril DressageJuly Flag and PoleJuly SELECTevent_name, DATENAME (MONTH, show_held) AS times_held_in_July FROMevent, show WHEREevent.show_id = show.show_id ANDshow.show_held = '&July&'
Result:
Msg 241, Level 16, State 1, Line 24
Conversion failed when converting date and/or time from character string.
BEGIN TRAN; INSERT INTO [dbo].[QuestManualProcess] Â Â Â Â Â Â ([ProcessFromDate] Â Â Â Â Â Â ,[LastProcessedFileDateStamp] Â Â Â Â Â Â ,[ProcessedOnDate] Â Â Â Â Â Â [code]....
Conversion failed when converting date and/or time from character string/
I am trying to write a stored procedure that loops through the list of user tables, gets the record count for each one and write a record to an audit table with DATE, TABLENAME, RECORDCOUNT.I keep getting an error "Conversion failed when converting date and/or time from character string".Here is the script...
DECLARE @table nvarchar(500) DECLARE @sql nvarchar(520) DECLARE CursorSelect CURSOR FOR select table_name from INFORMATION_SCHEMA.tables where table_name not like 'sys%' order by table_name
I've imported a CSV file into a table in SQL Server 2012. It's a large file, 140,000+ rows, so I couldn't covert it to Excel first to preserve the date format due to Excel's row limit. In the CSV file, there were 3 column with date data in "31-Aug-09" format, and the import automatically transformed these in "31AUG09" format (varchar(50)) in SQL Server. Now I need to convert these 3 columns from varchar to datetime so I could work with them in date format.
All source and target date fields are defined as data type "smalldatetime". The "select" executes without error though when used with "insert into" it fails with the error:
Msg 295, Level 16, State 3, Line 25: Conversion failed when converting character string to small date-time data type..I am converting from a character string to smalldatetime since the source and target date columns are "smalldatetime". All other columns for the source and target are nvarchar(255). I assume there is an implicit conversion that I don't understand. In a test, I validated that all dates selected evaluate ISDATE() to 1.
USE [SCIR_DataMart_FromProd_06_20_2014] GO IF OBJECT_ID ('[SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]', 'U') IS NOT NULL DROP TABLE [SCIR_DataMart_FromProd_06_20_2014].[dbo].[IdentifierLookup]
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
USE [Testing] GO /****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
