Time Conversion Formula

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.

View Replies


Current Date/time Formula

Hi,I would like to have a date_last_modified field for one of my SQLtables. Instead of coding my front end to keep up with this field isthere a way to user a formula for this column in Enterprise Manager sothat each time a record is created or updated the current date/timewill be inserted/updated? I tried using getdate() but then the fieldalways has the current time, which is not what I am looking for. I amlooking for the time the row is updated or created. Is this possible?Thank you!

View Replies View Related

Conversion Of Oracle Date Time To Sql Server Date Time In SSIS

This is driving me nuts..

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,



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.


View Replies View Related

Conversion For Time

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.

View Replies View Related

Time Conversion


I'm don't seem to be able to find any examples for creating a timesheet app.

For example I have the following columns in mind

Date datetime
StartTime datetime
FinsishTime datetime
Lunch int

I want the last column to be computed and do a datediff between startTime
& FinishTime - Lunch.

Can someone advise me on the correct syntax.

Thanks in advance.

View Replies View Related

Time Conversion

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!

View Replies View Related

IBM Time Conversion


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.

Any ideas?

View Replies View Related

Conversion: Time To Date

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?

View Replies View Related

Time Conversion Hiccup

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.

View Replies View Related

Date Time Conversion


I just had problem with Date Time conversion. Here is the example:

Select GetDate()

Result: 2000-08-02 23:50:15.280

Then I use Convert function:

Select Convert(DateTime,Cast(GetDate() as varchar), 101)

Result: 2000-08-02 23:50:00.000

What I expected to see is: 08/02/2000 and it was what I used to get.

Can anybody helpe to solve this problem? I am running sql server 7.0



View Replies View Related

Time Conversion Issue


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 ?

Thanks for your advices.

View Replies View Related

Julian Date Time 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.

View Replies View Related

Date An Time Conversion Problem

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.

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)
print 'Run'
print 'You cant run this'

Now here is the my table data:

datebegin datefin
------------------------ ------------------------
2004-12-25 00:00:00.0002005-01-25 00:00:00.000
2004-11-25 00:00:00.0002004-12-24 00:00:00.000
2005-02-25 00:00:00.0002005-03-25 00:00:00.000

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.

View Replies View Related

Date And Time Together Conversion To Yyyy:mm:dd Hh:mm:ss

I have 2 fields with data like
20040201 and 122235

Combined they need to make
2002-02-01 12:22:35

I can convert them separately to dates just fine. But when I try to combine them and convert missing something. here is what I have so far

select convert(datetime,(convert(datetime,[Dateproduced],112)+'
'+(left(timeproduced,2) + ':'+ substring(timeproduced,3,2)+':'+right(timeproduced ,2),120)))
from Demographic_staging

View Replies View Related

DTS Package Date/Time Conversion

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?

View Replies View Related

Date/Time Conversion Problem

After upgrading to SQL 7 we found that all our datetime fields now contain milliseconds. Our applications due extensive datetime validation and those routines are now failing because the date/times don't match up with milliseconds. Does anyone know if there is an easy way to turn off the milliseconds on a datetime field, or another easy solution to this problem?


Jim Franz

View Replies View Related

UNIX Time Conversion Functions

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:

For more information about SQL Server date/time conversions, refer to this link:
Date/Time Info and Script Links

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')


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:

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
create function dbo.F_DATETIME_TO_UNIX_TIME
( @DAY datetime )
returns int

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.
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)

if objectproperty(object_id('dbo.F_UNIX_TIME_TO_DATETIME'),'IsScalarFunction') = 1
begin drop function dbo.F_UNIX_TIME_TO_DATETIME end
create function dbo.F_UNIX_TIME_TO_DATETIME
( @UNIX_TIME int )
returns 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.

return dateadd(ss,@UNIX_TIME,25567)


Demo functions F_DATETIME_TO_UNIX_TIME and
F_UNIX_TIME_TO_DATETIME by converting a datetime
to UNIX time and back to datetime.

[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
Note = .a.note
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
DT = '2038-01-19 03:14:07.500',
Note = 'Date time function cannot convert - high end'
union all
DT = '1753-01-01 00:00:00.000',
Note = 'Min Datetime'
union all
DT = '9999-12-31 23:59:59.997',
Note = 'Max Datetime'
) a


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.


View Replies View Related

Date Time Conversion Problem

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:

"INSERT INTO tblUploadHeader (ExecutionGUID,
StartDateTime) VALUES ( '" + @[System::ExecutionInstanceGUID] + "', '" +
(DT_WSTR, 20) @[System::StartTime] + "' )"

which evaluates to

INSERT INTO tblUploadHeader (ExecutionGUID,
StartDateTime) VALUES ( '{C913A6EC-5DB9-405E-82DB-4F46DE454EEB}', '14/02/2006
11:53:32' )

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?
- Jerzy

View Replies View Related

Restoring During Daylight Saving Time Conversion

Here is an idle speculation question that came up, but I do not have an answer for.

At 2:00 AM on Oct. 31, we enter/leave (I never get it straight) Daylight Savings time. This means, we get to live through 2004-10-31 01:00:00.000 to 2004-10-31 02:00:00.000 twice, in somewhat rapid succession. The question is, what do you do if you need to restore a database to a point in time during that 2 hour window. One theory is to set a log mark at the second 2004-10-31 01:00:00.000, in order to differentiate the two hours. I am somehow not satisfied with that.

Like I said. This is an idle speculation, and could well be forgotten on Nov 1.

View Replies View Related

Datetime To Time Conversion With Default Date


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?


View Replies View Related

Data Conversion &&amp; Saving Takes Time

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)

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.

Could somebody help?

View Replies View Related

How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Hi all,

I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.

I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.

Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View Replies View Related

MDX Formula


Could you help me. I created a Report of the following form.

2005 2004
Sales 13000 12500
Variable Costs 9000 8750
Distribution Variable 1500 1423
Total Variable Costs 15756 14567
Variable Margin 12820 12000
% of Sales 591.69% 576%

But in order to obtain the colum for 2005 and 2004 I am using the following formula: Sum({Ytd()} ,[Versión].[Versión].&[1]). So when I try to obtain the row " % of sales" those % area acumulated too. When the result should be : 68.61 and 96%

What I need to do or what formula I need to use in order the % of Sales are not accumulated??? :


View Replies View Related


I am trying to build a database and I need to use the Formula field when creating a new coloumn!

I have built a table (tlbOrdersProducts)that contains te following fields:

orOrderId (int) FK
orProductId (varchar(50)) FK
orQuantity (int)
orPrice (float)

and another table (tlbProducts) that has the following fields:

prProductId (varchar(50))
prProductName (varchar(50))
prPrice (float)

Now I wanted to make a formula for the coloum tlbOrdersProducts.orPrice that automatically multiplies the tlbOrdersProducts.orQuantity * tlbProducts.prPrice

does anyone know how this can be done!!??

thanks for any sugestions and help!

View Replies View Related

SUM(IF( Formula

Having a hard time writing a formula in RS...

Trying to say if the funding date is equal to today or before the beginning of the month then sum the loan amount.

Looking for something like this SUM(IF(Funding Date,>=Date(),Loan Amount))

Know it not right but someone please help!

View Replies View Related

Formula In SQL

I am create a database and want to store some value automatically in some field i.e say i have 3 column 1) salary(int),2)tax(int) and 3rd field total salary(float) . i want to automatically fill total salary field as Column1-Column2 while i daont have any Idea How to do that?
please help me?
any idea or tutorial..example anything...

View Replies View Related

Formula In Sql Server

hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application

View Replies View Related

Getting Values Into A Formula

hi there.
I have asked this question before in a different section of the forum without much reply, and its probably because i was asking the wrong people.
I need to calculate a qouta for an election.
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota =  (count(Vote) + 1) / ((11) + 1);
My problem is this: how to i get the count(vote) value from the Sql Statement to the formula.

View Replies View Related

Formula Not Working

This does nothing:([rush24] + [rush6] + [addLocationsTotal] + 50)
This gives me my total correctly:([rush24] + [rush6] + 50)
All column are numeric(9) except the "total" column which is numeric(13).
Why is this happening?

View Replies View Related

Using Formula For Colum Name!!

i wonder what is the best approach to use !!!i have creditLimit column in Customer Table the default value will be 500 and this limit to allow users to send sms from my website ..... every month they will be allowed to send 500 sms referring to the credit limit column.... now !!if the user sent today some sms and after few days sent another 20 and after one week he sent 150  SMS as a total so that means he has only 350 SMS as credit to use this month!!!so what is the best approach to implement this solution ? shall i have another field in the table called 'CreditUsed' and that will be updated each time the user will send SMS and this value will be compared with the credit limit ORi use the formula for the column to calculate the credit left and do the maths !!!what is the best approach as you think ???thnaks for reading this question !!

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved