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.
First, let me start by saying I know the answer to this, but due to politics any answer I give will be viewed with some disdain and disbelief.
A Unix/Network [rtdpmin one our sister agencies is trying to solve a problem that really does not need to be solved, but ....
The sister agency uses Information Builder's WebFocus on Unix. When they try to read my data warehouse in SQL Server 2000 it times out on them (on their end and they claim they cannot fix it) (This is using the supplied WebFocus ODBC/JDBC driver for SQL Server). This has lead to accusations of us not letting them read the data.
After much gnashing of teeth over "if you do data and data analysis for a living, why do I have to show you how to set-up an ODBC connection" we have shown them how to connect to the data, read it, and transfer it using MS-ACCESS, EXCEL, and more importantly SPSS. This is not good enough because they cannot figure out how to put it into WebFocus.
Their network person has come-up with the brilliant idea of reading the .mdf directly by installing Windows for Unix on the Unix side and then just pointing to the .mdf file and "reading it ".
Unless I have missed something somwhere (I will admit possible), I know you need a driver to read MS-SQL and that driver in every example I have seen could care less about the physical location of the .mdf file. It wants to know what database, what server (ip or name) and what security/login to use.
Could someone give me a more "technical answer" or even Microsoft's party line so I do not have this person mucking around with my production server trying to acomplish the impossible.
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
There are a lot of questions posted on SQLTEAM asking how to find the beginning of various time periods. The script will create and demo 14 functions that return a datetime for the beginning of a time period relative to the datetime value passed in parameter @DAY.
I put together this script to create these functions for several reasons: 1. To allow people to find them on their own without having to post a question. 2. To allow posted questions to be answered with a reference to this script. 3. To document algorithms that work for the widest possible range of datetime values. All except for the Century and Decade functions work for any datetime value from 1753/01/01 00:00:00.000 through 9999/12/31 23:59:59.997. The Century is limited to datetimes from 1800/01/01 forward, because 1700/01/01 is not valid in SQL Server. The Decade function is limited to datetimes from 1760/01/01 forward, because 1750/01/01 is not valid in SQL Server. 4. And last, you can actually use them on your application.
There is a separate post for function dbo.F_START_OF_WEEK to find the first day of the week at this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
This script was tested with SQL Server 2000 only.
I posted a script for End Date of Time Period Functions here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
Other Date/Time Info and Script Links: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
Edit 2006-11-01: Added dbo.F_START_OF_X_MIN( @DAY ) at the suggestion of Peter.
Edit 2007-02-24: Modified the following functions to use a simpler algorithm, that is shorter, runs faster, and is more suited for use with in-line code: dbo.F_START_OF_30_MIN( @DAY ) dbo.F_START_OF_20_MIN( @DAY ) dbo.F_START_OF_15_MIN( @DAY ) dbo.F_START_OF_10_MIN( @DAY ) dbo.F_START_OF_05_MIN( @DAY )
*/ go if objectproperty(object_id('dbo.F_START_OF_CENTURY'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_CENTURY end go create function dbo.F_START_OF_CENTURY ( @DAY datetime ) returns datetime as /* Function: F_START_OF_CENTURY Finds start of first day of century at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes >= 1800-01-01 00:00:00.000 Returns null if @DAY < 1800-01-01 00:00:00.000 */ begin
end go if objectproperty(object_id('dbo.F_START_OF_DECADE'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_DECADE end go create function dbo.F_START_OF_DECADE ( @DAY datetime ) returns datetime as /* Function: F_START_OF_DECADE Finds start of first day of decade at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes >= 1760-01-01 00:00:00.000 Returns null if @DAY < 1760-01-01 00:00:00.000 */ begin
end go if objectproperty(object_id('dbo.F_START_OF_YEAR'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_YEAR end go create function dbo.F_START_OF_YEAR ( @DAY datetime ) returns datetime as /* Function: F_START_OF_YEAR Finds start of first day of year at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(yy,datediff(yy,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_QUARTER'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_QUARTER end go create function dbo.F_START_OF_QUARTER ( @DAY datetime ) returns datetime as /* Function: F_START_OF_QUARTER Finds start of first day of quarter at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(qq,datediff(qq,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_MONTH'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_MONTH end go create function dbo.F_START_OF_MONTH ( @DAY datetime ) returns datetime as /* Function: F_START_OF_MONTH Finds start of first day of month at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(mm,datediff(mm,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_DAY'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_DAY end go create function dbo.F_START_OF_DAY ( @DAY datetime ) returns datetime as /* Function: F_START_OF_DAY Finds start of day at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes */ begin
return dateadd(dd,datediff(dd,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_HOUR'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_HOUR end go create function dbo.F_START_OF_HOUR ( @DAY datetime ) returns datetime as /* Function: F_START_OF_HOUR Finds beginning of hour for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(hh,datediff(hh,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_30_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_30_MIN end go create function dbo.F_START_OF_30_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_30_MIN Finds beginning of 30 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_20_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_20_MIN end go create function dbo.F_START_OF_20_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_20_MIN Finds beginning of 20 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_15_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_15_MIN end go create function dbo.F_START_OF_15_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_15_MIN Finds beginning of 15 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_10_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_10_MIN end go create function dbo.F_START_OF_10_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_10_MIN Finds beginning of 10 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_05_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_05_MIN end go create function dbo.F_START_OF_05_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_05_MIN Finds beginning of 5 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_X_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_X_MIN end go create function dbo.F_START_OF_X_MIN ( @DAY datetime, @INTERVAL int ) returns datetime as /* Function: F_START_OF_X_MIN Finds beginning of @INTERVAL minute period for input datetime, @DAY. If @INTERVAL = zero, returns @DAY. Valid for all SQL Server datetimes. */ begin
-- Prevent divide by zero error if @INTERVAL = 0 return @DAY
declare @BASE_DAY datetime set @BASE_DAY = dateadd(dd,datediff(dd,0,@Day),0)
end go if objectproperty(object_id('dbo.F_START_OF_MINUTE'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_MINUTE end go create function dbo.F_START_OF_MINUTE ( @DAY datetime ) returns datetime as /* Function: F_START_OF_MINUTE Finds beginning of minute for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_SECOND'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_SECOND end go create function dbo.F_START_OF_SECOND ( @DAY datetime ) returns datetime as /* Function: F_START_OF_SECOND Finds beginning of second for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
insert into @test_dates (DT) select DT = getdate()union all select '17530101 00:00:00.000'union all -- Test start of Decade cutoff select '17591231 23:59:59.997'union all select '17600101 23:04:59.997'union all -- Test start of Century cutoff select '17991231 23:59:59.997'union all select '18000101 00:00:00.000'union all -- Test start of Decade and Century select '19000101 00:00:00.000'union all select '19001231 23:59:59.997'union all select '19400101 00:00:00.000'union all select '19491231 23:59:59.997'union all select '19900101 00:00:00.000'union all select '19991231 23:59:59.997'union all -- For start of Hour testing select @lyear+' 00:00:00.000'union all select @lyear+' 00:59:59.997'union all select @lyear+' 01:00:00.000'union all select @lyear+' 01:59:59.997'union all select @lyear+' 12:00:00.000'union all select @lyear+' 12:59:59.997'union all select @lyear+' 17:00:00.000'union all select @lyear+' 17:59:59.997'union all select @lyear+' 23:00:00.000'union all select @lyear+' 23:59:59.997'union all -- For start of Month, Quarter, and Year testing select @year+'0101 00:00:00.000'union all select @year+'0131 23:59:59.997'union all select @year+'0201 00:00:00.000'union all select @year+'0228 23:59:59.997'union all select @year+'0301 00:00:00.000'union all select @year+'0331 23:59:59.997'union all select @year+'0401 00:00:00.000'union all select @year+'0430 23:59:59.997'union all select @year+'0501 00:00:00.000'union all select @year+'0531 23:59:59.997'union all select @year+'0601 00:00:00.000'union all select @year+'0630 23:59:59.997'union all select @year+'0701 00:00:00.000'union all select @year+'0731 23:59:59.997'union all select @year+'0801 00:00:00.000'union all select @year+'0831 23:59:59.997'union all select @year+'0901 00:00:00.000'union all select @year+'0930 23:59:59.997'union all select @year+'1001 00:00:00.000'union all select @year+'1031 23:59:59.997'union all select @year+'1101 00:00:00.000'union all select @year+'1130 23:59:59.997'union all select @year+'1201 00:00:00.000'union all select @year+'1231 23:59:59.997'union all -- Test start of 5, 10, 15, 20, and 30 min testing select @today+' 23:04:59.997'union all select @today+' 23:09:59.997'union all select @today+' 23:14:59.997'union all select @today+' 23:19:59.997'union all select @today+' 23:24:59.997'union all select @today+' 23:29:59.997'union all select @today+' 23:34:59.997'union all select @today+' 23:39:59.997'union all select @today+' 23:44:59.997'union all select @today+' 23:49:59.997'union all select @today+' 23:54:59.997'union all select @today+' 23:59:59.997'union all select '99991231 23:59:59.997' order by 1
-- Convert dates in @test_dates table to test F_START_OF functions
select TYPE = 'CENTURY' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_CENTURY( DT ),121) from @test_dates order by DT
select TYPE = 'DECADE' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_DECADE( DT ),121) from @test_dates order by DT
select TYPE = 'YEAR' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_YEAR( DT ),121) from @test_dates order by DT
select TYPE = 'QUARTER' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_QUARTER( DT ),121) from @test_dates order by DT
select TYPE = 'MONTH' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_MONTH( DT ),121) from @test_dates order by DT
select TYPE = 'DAY' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_DAY( DT ),121) from @test_dates order by DT
select TYPE = 'HOUR' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_HOUR( DT ),121) from @test_dates order by DT
select TYPE = '30_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_30_MIN( DT ),121) from @test_dates order by DT
select TYPE = '20_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_20_MIN( DT ),121) from @test_dates order by DT
select TYPE = '15_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_15_MIN( DT ),121) from @test_dates order by DT
select TYPE = '10_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_10_MIN( DT ),121) from @test_dates order by DT
select TYPE = '05_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_05_MIN( DT ),121) from @test_dates order by DT
select TYPE = 'MINUTE' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_MINUTE( DT ),121) from @test_dates order by DT
select TYPE = 'SECOND' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_SECOND( DT ),121) from @test_dates order by DT /* End of test script */
We are using [DATE] [TIME] functions in SQL Server 2000 agent jobs and SQL Server use to translate it to current data and time functions but in SS2005 it is not replacing the functions and we are getting filename as "test_DATE_TIME" whereas we expect "test_20071204_130000" Do we have any new functions as replacement?
Thanks --rubs
Following is the code we are using: declare @name nvarchar(100) declare @name1 nvarchar(100) set @name1 = 'test_[DATE]_[TIME]' set @name = 'c:ackup' + @name1 + '.bak' backup database test to disk = @name
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.
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 ?
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.
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.
