T-SQL (SS2K8) :: Getting UTC Date From Integer?
Dec 29, 2014
I'm trying to get the UTC date from an integer. See my SQL below:
Declare @unix_date bigint
Set @unix_date = 3499288964
SELECT dateadd(day, @unix_date/(86400), dateadd(second, @unix_date
% (86400), '19700101'))
It returns '2080-11-20 00:42:44.000'. This is dead on except for the year (which should be 2014). I was thinking maybe my bigint value of 3499288964 was milliseconds or microseconds so I adjusted the seconds value (86400) in the select statement to reflect milliseconds (86400000) with no success and microseconds (86400000000) with no success as both of those gave incorrect results. Closest I got was with the seconds (86400) which of course returns the incorrect year.
View 2 Replies
ADVERTISEMENT
Jul 15, 2014
As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.
I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.
Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.
It leads to massive implicit conversions and I am trying to change this.
To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.
Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.
View 4 Replies
View Related
Jul 20, 2015
Working on a new database where the Date and Time are stored in a Date Time Field.
Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:
transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
  dbo.IntegerToDate(at.transDate) as transDate
  * That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
View 3 Replies
View Related
Jul 21, 2014
When I use the following SQL with the variable @CutOFFTID the second select statement query takes several minutes. If i run it replacing the value in the where clause with the acutal value of the variable it runs instantly.
DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROMdbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)
DECLARE @MinPeriod int
SELECT @MinPeriod = Min(period)
FROM agr.dbo.atrans
WHERE agrtid >@CutOffTid
View 9 Replies
View Related
Jun 28, 2015
how to round the nearest value after round of decimal and return integer.
declare @data decimal(18,2)
set @data = 5.55
-- output
select '5'
set @data = 5.58
-- output
select '6'
View 3 Replies
View Related
May 28, 2007
My source database stores dates as integers (e.g. 20070101). I need to convert to a "real" date for my target system.
I'm guessing I need to create a derived column - could someone help me out with the appropriate expression?
Thanks,
JG
View 12 Replies
View Related
Apr 4, 2014
I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
View 1 Replies
View Related
May 3, 2007
year=2004
month=1
day=1
how to create a date from that?
date(year, month, day) does not seem to work.
Thanks.
Jeff
View 4 Replies
View Related
Aug 8, 2006
I want to for that format the date in YYYYMMDD and MMDDYY, with no '-' as data type is integer
I have used the following code (not the conversion function as I don€™t need Hyphen '-')
for YYYYDDMM
SET @DATE = CONVERT(INT,(CONVERT(VARCHAR(4),DATEPART(YYYY,GETDATE())) +
CONVERT(VARCHAR(4), DATEPART(MM,GETDATE())) +
CONVERT(VARCHAR(4), DATEPART(DD,GETDATE())) ))
& for MMDDYY
SET @DATEUS = CONVERT(INT,(CONVERT(VARCHAR(3),DATEPART(MM,GETDATE()))+
CONVERT(VARCHAR(3),DATEPART(DD,GETDATE())) +
SUBSTRING(CONVERT(VARCHAR(4), DATEPART(YY,GETDATE())),3,4) ))
I am getting the result
YYYYMMDD= 200688
MMDDYY =8806
but i want result in
YYYYDDMM = 20060808
MMDDYY = 080806
note: I need to convert in integer, finally, caz database data type is integer.
can any one give me solution
waiting for quick reply
regards,
Anas
View 3 Replies
View Related
Mar 27, 2008
Hi,
I have checked the forums but couldnt find exact term.
I have written tsql statement but I dont know how to use that in SSIS
this is a update statement in oledb command component...
I wanna get integer like YYYYMMDD ( 20080325)
UPDATE [dbo].[d_cust] SET [per_xxx] = ? ,
[per_valid]=(select cast(CONVERT(varchar ,DATEADD(dd,-1,getdate()),112 )as int))
View 3 Replies
View Related
Aug 4, 2015
I am trying to add integer value to the datetime to get the configurable "date".But it is not working out. Here is the code for it.
declare @Id int;
exec @Id = up_GetConfigurableDayInterval
print getdate() + @Id
The sp "up_GetConfigurableDayInterval", will return number of days to add the the current date time. But print getdate() + @Id , is not producing the updated result.But when ever i have replaced the variable "@Id" with a value, Say "5", it is producing the expected result.I have also tested the above code by the following:-
create table Temp(value datetime);
insert into Temp values(getdate() + @Id);
select * from Temp
But it is not having the new added datetime.
View 12 Replies
View Related
Oct 16, 2014
In VBA, CLng(Now) will return the integer portion of a date CLng(Now) returns 41928, CDate(41928) then returns 10/16/2014. Is there something equivalent in SQL Server that will allow me to convert an integer value to a date?
In short, how can I convert a 100 year date to Gregorian (any format)?
View 3 Replies
View Related
Sep 10, 2014
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
View 5 Replies
View Related
Mar 14, 2014
Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.
View 9 Replies
View Related
Jul 23, 2014
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
View 9 Replies
View Related
Oct 12, 2015
I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).
View 2 Replies
View Related
May 12, 2014
I have a table containing some events. Those events all have a start date and an end date.
CREATE TABLE #events
(
eventID int,
eventname char(30),
startdate date,
enddate date
[Code] ....
When looking at the data some days have multiple events. Now I want to generate a new table that show all the dates in this month showing the number of running events for that specific day.
View 9 Replies
View Related
Jun 17, 2014
i am trying to convert a string like this 'le dd/mm/yyyy' into a datetime.I have removed the 'le ' part and used covert(datetime, 'dd/mm/yyyy',103) to convert into datetime. This works for example for 'le 22/11/1799' but for 'le 09/11/1716' it does not work.
select convert(datetime,RIGHT('le 22/11/1799', LEN('le 22/11/1799') - 3), 103) -> it works
select convert(datetime,RIGHT('le 09/11/1716', LEN('le 09/11/1716') - 3), 103) -> it does not work
View 3 Replies
View Related
Jun 26, 2014
I would like to get the first and last day of any month in a given date range.
Ex: Display the first and last day of the months between @startDate and @EndDates.
Input Params= @StartDate='2016-06-21 16:57:11.093'
@EndDate = '2016-09-30 00:00:00.000'
OutPut should be:-
MonthStartDateMonthEndDate
1/06/201630/06/2016
1/07/201631/07/2016
1/08/201631/08/2016
1/09/201630/09/2016
View 1 Replies
View Related
Sep 5, 2014
I'm importing dates into a table with Bulk insert
SET DATEFORMAT DMY
it works with dates e.g. "14/01/2009"
However sometimes I get dates in the format
"Fri 14/01/2009"
What is the best way to convert these.
I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields
LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))
View 9 Replies
View Related
Sep 8, 2014
how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.
Also, the solution should not be too cumbersome because it will be used in a high volume environment.
View 5 Replies
View Related
Dec 10, 2014
I have two dates. How do I get the one that is the lowest. One may be null. I don't want null unless they are both null
I tried..
DECLARE @Handle date
SELECT @Handle = dbo.getTrkLeastDate('2014-12-09',NULL)
print @Handle
ALTER FUNCTION [dbo].[getTrkLeastDate] (@d1 date, @d2 date)
RETURNS datetime
[Code] .....
View 4 Replies
View Related
Dec 23, 2014
I have a date column with 1900-01-01 value, I am trying to get the min(date) without 1900-01-01.My problem here is: there are some records with both 1900-01-01 and real date. How do I get the MIN of the real date without getting 1900-01-01. Below is my query sample.
CASE WHEN MIN(CAST(PROJECTEDSTARTDATE AS DATE)) = '1900-01-01' THEN NULL ELSE MIN(CAST(PROJECTEDSTARTDATE AS DATE)) END as 'ProjectStartDate'
View 3 Replies
View Related
Jan 14, 2015
I have a query that will go into an ssis package (eventually). The package will run every night at 3am. I need to capture the last 24 hours of by using something like:
SELECT worktype, changedate, woclass
where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0
[highlight=#ffff11]and changedate between '2015-01-13 03:00:00' and '2015-01-14 03:00:00'[/highlight]
I know I am not doing the between correctly to get the changedate between the last 24 hours. Is there a way to correct this so that I am only getting the change date that is between 3am today and 3am yesterday on any given day I happen to run this?
View 7 Replies
View Related
Nov 8, 2012
I need creating date of birth using ID number the ouput that im looking is a follows
e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01
e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01
The desired format I need is to take the above and create date of birth with the below format as required by the application used.
01 Jan 1980
01 Jan 2000
View 9 Replies
View Related
Sep 13, 2013
I have a group of date ranges and wanted to identify all of the date gaps within the ranges, outputting the dates as another date range dataset.
Example dataset SQL below:
CREATE TABLE #test (daterow int identity, obj_id int, datestart DATETIME, dateend DATETIME)
INSERT INTO #test
SELECT 1, '20130428', '20130523'
UNION
SELECT 1, '20130526', '20130823'
[Code] ....
I would expect a dataset to be returned consisting of:
1, 24/05/2013, 25/05/2013
1, 24/08/2013, 25/08/2013
2, 16/05/2013, 24/05/2013
I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...
View 9 Replies
View Related
Mar 12, 2014
How to get this out put.
Details:
declare @deadline Datetime = '2014-03-23 15:30:10.000'
SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert
----With this I am able to produce that like
----o/p: Mar 23 2014 3:30PM
declare @deadline1 Datetime = '2014-03-03 15:30:10.000'
SELECT CONVERT(VARCHAR(24),@deadline1, 100) AS DateConvert
--o/p: Mar 3 2014 3:30PM
--expected O/p: Mar 03 2014 03:30PM
What is the correct date format to achieve this.
View 2 Replies
View Related
Apr 17, 2014
I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .
Create Table Test
(Tsq INT IDENTITY (1,1),
Data Varchar (150),
ts datetime,
Tpkt_type int)
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')
[code]....
Expected Output
---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'
View 9 Replies
View Related
Apr 30, 2014
I have a table with 5000 rows history about working and retired time.
It is the several rows from table(User, StartDate, EndDate):
User1 2011-05-09 00:00 2014-01-17 00:00
User2 2012-07-01 00:00 2012-08-20 00:00
User2 2013-08-26 00:00 2013-09-02 00:00
User2 2013-10-07 00:00 NULL
User3 2013-09-01 00:00 2014-04-07 00:00
User3 2014-04-08 00:00 NULL
How many users have worked and have retired by years and months,
Example:
2011-01 working 2000
2011-02 retired -50
2011-02 working 1950
2011-02 retired -27
and etc,,
Does it need to join to a time dimension table?
View 7 Replies
View Related
Jun 18, 2014
Below SQL gives the results of 2 columns and I need the DepartmentandDate column to be ORDER BY on date desc for that CID. Expected result is in the screenshot(attachment)...
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(25))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2014-01-14 00:00:00.000','B is alphabet'),
[Code] ....
View 6 Replies
View Related
Jun 20, 2014
I need the script for the following schedule.
if the repayment start date = 05-01-2016 and installment=5 then result will be
05-01-2016
05-02-2016
05-03-2016
05-04-2016
05-05-2016
if the repayment start date = 30-01-2016 and installment=5 then result will be
30-01-2016
29-02-2016
30-03-2016
30-04-2016
30-05-2016
if the repayment start date = 31-01-2016 and installment=5 then result will be
31-01-2016
29-02-2016
31-03-2016
30-04-2016
31-05-2016
if the repayment start date = 29-02-2016 and installment=5 then result will be
29-02-2016
31-03-2016
30-04-2016
31-05-2016
30-06-2016
if the repayment start date = 28-02-2015 and installment=5 then result will be
28-02-2015
31-03-2015
30-04-2015
31-05-2015
30-06-2015
I have find some ways but unable to make it.
View 7 Replies
View Related
Jul 16, 2014
I have 2 tables:
Table Transaction
-----------------
EmpID TransDate
00001 1/1/2014
00001 1/2/2014
00001 1/3/2104
00001 1/4/2014
00001 1/5/2014
00001 1/6/2014
00001 1/15/2014
00001 2/1/2014
00001 2/2/2014
00001 2/20/2004 ....
Table Master
---------------------------
EmpID EffectiveDateFr Group
00001 1/1/2014 A
00001 1/5/2014 B
00001 1/9/2014 C
00001 2/1/2014 B
00001 2/20/2014 A ....
I want to create query the output should be:
EmpID TransDate Group
00001 1/1/2014 A
00001 1/2/2014 A
00001 1/3/2104 A
00001 1/4/2014 A
00001 1/5/2014 B
00001 1/6/2014 B
00001 1/15/2014 C
00001 2/1/2014 B
00001 2/2/2014 B
00001 2/20/2004 A
View 4 Replies
View Related
Jul 31, 2014
Any script handy to fill in a century into a date string. Right now, I'm getting dates in the following format:
7/26/29 = converts to 2029.
I'm looking for a SQL statement that will now to put a 19 or 20 in the century.
View 9 Replies
View Related
