SQL Server 2012 :: Increment Day To Timestamp When Time Is After 00:00
Jun 29, 2015
I have a series of time from 08:10 till 00:55 and a single date in a seperate column. What I need to have is associate the date with each time from 08:10 and when it comes after 23:55 the date needs to be incremented to the next day.
something like below
29Jun2015 08:10
29Jun2015 08:15
29Jun2015 08:20
29Jun2015 08:25
29Jun2015 08:30
29Jun2015 08:35
29Jun2015 08:40
.....
.....
....
....
29Jun2015 23:55
30Jun2015 00:00
30Jun2015 00:05
30Jun2015 00:10
30Jun2015 00:15
30Jun2015 00:20
I tried to add the based on the datepart minute however its getting added only to 00:00 and not after that.
View 9 Replies
ADVERTISEMENT
Jul 1, 2015
I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.
To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.
The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below
CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]
By this above code i have created the expected datetime however
I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like
case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)
This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.
I have provided a screenshot of my data and a expected date column for reference.
Candidate StartDateStartTimeExpected DateTime Timepoint
1 20141020 1000 2014-10-20 10:00:00 0 HR
1 201410201200 2014-10-20 12:00:00 02 HR
1 201410201400 2014-10-20 14:00:00 04 HR
1 201410201800 2014-10-20 18:00:00 08 HR
1 201410200000 2014-10-21 00:00:00 12 HR
1 201410201200 2014-10-21 12:00:00 24 HR
2 20141020 1100 2014-10-20 11:00:00 0 HR
[Code] ....
I have also attached the data for reference.
View 9 Replies
View Related
Oct 27, 2014
I've got a question about the UNIX timestamp solutions, I am using this one:
select convert(datetime, switchoffset(convert(datetimeoffset, dateadd(second, start_date, '19700101')), datename(TzOffset, sysdatetimeoffset())))
from x
This works great for today (wintertime), but when I query the past, say last week, it will still hold the current offset of my server GMT+1, where it was GMT+2.
Is there a way to get always the right date/time from a sql-query?
View 0 Replies
View Related
Feb 17, 2014
I have two tables with this info:
TABLE 1
COL1 COL2 COL3
AAA BBB CCC
QQQ WWW EEE
AAA SSS DDD
WWW EEE RRR
BBB BBB BBB
TABLE 2
COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
I want to insert TABLE 1 into TABLE 2 with a query that will auto increment to COL4 looking like this:
COL1 COL2 COL3 COL4
b b b 343
a a a 344
c c c 345
d d d 346
e e e 347
AAA BBB CCC 348
QQQ WWW EEE 349
AAA SSS DDD 350
WWW EEE RRR 351
BBB BBB BBB 352
I know this can be done easily by just altering the column to have an auto-increment datatype, but I currently cannot do that at this moment.
View 5 Replies
View Related
Jun 19, 2007
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
View 6 Replies
View Related
Jun 5, 2002
SQL7, sp3
I run a backup each night via a job, using a backup set. The question/concern is: the timestamp on the physical backup set file doesn't not change but SQL says in the history of the job that the backup is successful. I checked the system time on the server and it's current, it's todays date, but again last night's backup says May22. Should this be cause for alarm? Bug??
thanks,
View 2 Replies
View Related
Sep 4, 2005
Hi
Could anyone tell me how can I set auto increment to a field in MSSQL at design time? So that whenever I insert a new record from VB, it will insert that new record incrementing the field which is not a primary key automatically.
Thank you very much for everyone who are constantly helping me...
View 3 Replies
View Related
Mar 9, 2014
I am developing asp.net application with sql server database
I have table FinancialYear
caseno(unique) finyear
1 2013-2014
2 2013-2014
3 2013-2014
1 2012-2013
2 2012-2013
3 2012-2013
For financial year-2013 to 2014. if i insert record ( select max(caseno) +1).
insert into finanancialYear values(max(caseno) +1,2013-2014)
if i delete record caseno:3 from the table,
select max(caseno)+1 will be 3
Which is already deleted and again that record with caseno 3 will be inserted
For every financial year in 'FinancialYear' table,.caseno will be reset to 1
And if I add new record in finyear 2012-1013
then max(caseno) will be last value of caseno of that financial year 2012-2013
Again insert becomes
insert into finanancialYear values(max(caseno) +1,2012-2013)
I have given the data.
View 5 Replies
View Related
Apr 7, 2014
I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
View 5 Replies
View Related
Apr 3, 2008
I have two fields DSRHADTI which is an isodate and DSRHTIME which is 8 char time field in format 10.31.00. I want to take both these fields and put them into a field that is database timestamp so I have converted DSRHDATI to 10 character field. I am then trying to use substring to put both into 18 character field using derived column transformation editor. but it does not like the below. It's red syntax error what am I missing.
(SUBSTRING(Copy of DSRHDATI,1,4) +' /' + SUBSTRING( Copy of DSRHDATI,6,2) + '/ ' + SUBSTRING(Copy of DSRHDATI,9,2)) + SUBSTRING(DSRHTIME,1,2) + '.' + SUBSTRING(DSRHTIME,4,2) + '.' + SUBSTRING(DSRHTIME,7,2)
One I get the above to work I plan on convert 18 char to datetimestamp.
Am I on the right track on how to do this?
View 16 Replies
View Related
Mar 18, 2014
I have a question regarding the rowversion / timestamp column. I want to use it to track changes in some tables and I created a non clustered index on the timestamp column (with three includes) to get a better performance while searching / gathering the data. It works very well, but I experienced a big performance hit on the inserts and my first thoughts are that it has something to do with this index.
I know that this NCI has to be updated with every insert and so this could have an impact on performance, but we have a lot of other indexes as well, which are also get updated with every insert. Why this index / column has such a big impact? Our application use a lot of dynamic cursors, maybe it has something to do with that?
View 4 Replies
View Related
Jul 14, 2014
What sql data type would fit these value? This is timestamp field in the ERP.
2013-05-24-20.03.46.843480
View 3 Replies
View Related
Mar 18, 2015
I want to write a stored procedure or a simple T-SQL snippet that takes a parameter "Directory Path" and gives me the name and datetimestamp associated with each of the files within that directory. I can not use xp_cmdshell since it is not allowed. CLR integration is not enabled on the server either.
Is there any other way at all to achieve this directly from SQL Server using T-SQL?
View 3 Replies
View Related
Feb 18, 2014
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
View 7 Replies
View Related
Sep 15, 2014
When I have a record that I copy and paste (Access 2010 datasheet view) into a new record with one column having a unique constraint , I display message "Cannot insert duplicate code" then if not all values in the record are inserted , I get a #deleted in all columns Refreshing , I find that the id is incremented by 2 instead of one.
That is if the previous record has ID 20 , the #deleted record has ID 22 .
avoid the #deleted which happens if not all columns are filled.
View 9 Replies
View Related
Nov 15, 2013
I am developing a form for a mortgage company. There can be any number of borrowers on a given loan, and the business has asked that this form return only 2 borrowers at a time for a loan. For example, if there are 3 borrowers for a loan, they want the first copy of the form to print the first 2 borrowers and then another copy of the form to print the 3rd. No matter how many copies are printed, they want the borrower information to be labeled as 'Borrower1' xyz and 'Borrower2' xyz. Also, there will be a LOT more fields returned on the real form, so the sample information below is very simplified test data.
Sample Data:
CREATE TABLE #t (LoanID VARCHAR(5), BorrowerName VARCHAR(20), BorrowerOrder INT);
GO
INSERT INTO #t VALUES
('::E', 'John Smith', 0)
, ('::E', 'Jane Smith', 1)
, ('::E', 'Rob Jackson', 2)
, ('AF_CF', 'Sloan Burton', 1)
[code]...
I don't want that 2nd record to return. This result is what makes me think of gaps and islands, but I don't know if the 2nd record is really an island since it's (1) not stored this way...it's returning this way because of the query and (2) it's not sequential data..I tried restricting this by putting this into a CTE and then returning only the odd numbered records like I have below. This runs pretty quickly when dealing with one loan. But...I am concerned that the CTE will be slow when we run batches of loans.
Attempt with CTE:
--With CTE
;WITH cte AS
(SELECT
Borrower1 = BorrowerName
, Borrower2 = LEAD(BorrowerName) OVER(ORDER BY BorrowerOrder)
, RowNumber = ROW_NUMBER() OVER(ORDER BY BorrowerOrder)
[code]...
Is there a better, cleaner way to do this? Or is the CTE the best way to go?
View 8 Replies
View Related
Jun 15, 2015
I have a column that is an int field and it holds the number of seconds that corresponds to a time during the day. Example being that 24331 will be 6:45:31 AM and 67531 will be 6:45:31 PM. I'm looking for code that will show the time in AM/PM format and with the AM and PM at the end of time.
View 5 Replies
View Related
Apr 25, 2014
Sample Table
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.
View 7 Replies
View Related
Dec 3, 2014
I've got a column that holds a time value in Integer form as a number from midnight (IE military time) with no leading 0s or colons.
0 = 00:00
30 = 00:30
...
1700 = 1700 etc...
How can I convert this to a real time field?
View 9 Replies
View Related
Mar 9, 2015
I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.
CPUTM = 335:55:20.97 duration time
CPUZIPTM = 0:00:01.96 duration time
select * FROM [SMF_DATA].[dbo].[System_Management_Facility]
WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')
View 2 Replies
View Related
Aug 4, 2015
I have an asp.net 4.5 webpage that has a detailsview on it that has a bound field that points to a SQL Server 2012 datetime field (CompDate). This field is supposed to capture date AND time of completion of a record (this is a data gathering project).
<asp:BoundField DataField="CompDate" HeaderText="CompDate" SortExpression="CompDate" Visible="False" />
The code that inserts the datetime:
If e.NewValues.Item("Complete") = True Then
Dim dt As DateTime = Now
e.NewValues("CompDate") = dt
e.NewValues("UNAME") = Membership.GetUser.ToString
End If
Everything works great, except when the captured date/time is sent to sql server, the time portion is getting dropped and only zeros are getting stored in the time portion.
Results:
CompleteUNameCompDate
1 CarlR 2015-08-04 00:00:00
I've tried different formatting options and converting to string..
View 0 Replies
View Related
Sep 15, 2015
I have string as '1-12-2012', i wanted to convert it into date as format '2012-12-01'. I used the function as cast('1-12-2012' as Date) it's working but for a set of values where Null occurs it gives error as conversion failed when casting date and/or time from character string.
View 5 Replies
View Related
Dec 26, 2014
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
View 2 Replies
View Related
Apr 17, 2014
How to convert bigint time to datetime (CST)
Declare @MyBigIntTime BIGINT =1397750400000
View 9 Replies
View Related
Jun 5, 2014
I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).
These are converted into a smalldatetime like this:
CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]
This code has been in place for years...and we stick the date on later from another column.
But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".
My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.
If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.
I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?
View 9 Replies
View Related
Mar 10, 2015
I am looking to be able to add 15 minutes to a time value that is in character format. here is sample data:
0530
0545
0600
0615
0630
0645
0700
0715
0730
0745
Whenever there is a leading zero, I need to preserve that as well. Here is an example of what I an looking for:
0545 + 15 = 0600
0600 + 15 = 0615
1345 + 15 = 1400
View 9 Replies
View Related
Jun 23, 2015
date time s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date time s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400 3
6/9/2015 11:56:26 TestSite 900 2
6/12/2015 12:56:26 TestSite 700 1
View 5 Replies
View Related
Jul 2, 2014
I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/
IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL
DROP TABLE [dbo].[#TestProject2]
GO
CREATE TABLE [dbo].[#TestProject2](
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TestProject2 ON
INSERT INTO #TestProject2
("ID","PersonID", "PercentLoad","MonthID")
SELECT 1,123456,0,1 UNION ALL
[Code] ....
EXPECTED RESULT:
Person ID StartMonth EndMonth LOADPCT
123456 3 4 .5
123456 5 6 1
654321 1 2 1
654321 4 4 .5
654321 5 6 .75
654321 7 9 .5
View 5 Replies
View Related
Jul 10, 2014
Looking for returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance.
Entry might look like:
(datetime) date: 7/10/2014
(int) start-time 820
(int) end-time 1000
(int) duration 20
The result would needed:
(datetime) 7/10/2014 08:20:00
(datetime) 7/10/2014 08:40:00
(datetime) 7/10/2014 09:00:00
(datetime) 7/10/2014 09:20:00
(datetime) 7/10/2014 09:40:00
View 8 Replies
View Related
Apr 19, 2015
I am having 100 of flat files need to load in respective staging table.I want to create table on run time as per filename input.suppose if input filename is ABC then table name should be Staging_ABC if file name is XYZ then it should be Staging_XYZ.Table structure is below need to create at run time
CREATE TABLE Staging_'Filename'(
[COL001] [varchar](4000) NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoadDate] [datetime] NOT NULL default getdate()
)
View 2 Replies
View Related
Apr 28, 2015
I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.
Sample Table
ID DeviceID Temp InsertDate
1 10 55 04-28-2015 8:00 AM
2 65 74 04-28-2015 8:00 AM
3 44 23 04-28-2015 8:00 AM
4 10 87 04-28-2015 8:01 AM
5 65 65 04-28-2015 8:01 AM
View 3 Replies
View Related
Jun 19, 2015
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test
where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest;
USE SpecTest
GO
CREATE TABLE [dbo].[Spec1](
[Spec1ID] [int] IDENTITY(1,1) NOT NULL,
[Channel] [int] NOT NULL,
[Code] ....
Here is some dummy data to use
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
[Code] ....
Example desired results (I hope the formatting works)
Channel | Timestamp | Lambda | Power | Elapsed_Time
______________________________________________________________
273 | '2014-05-29 14:44:17.713', | 800, | '-64.91' | 0
273 | '2014-05-29 15:05:09.507', | 800, | '-64.91' | 00:20:51
273 | '2014-05-29 15:26:00.520', | 800, | '-64.91' | 00:41:42
273 | '2014-05-29 16:28:34.213', | 800, | '-64.91' | 01:44:16
273 | '2014-05-29 16:49:25.853', | 800, | '-64.91' | 02:05:08
273 | '2014-05-29 17:10:17.010', | 800, | '-64.91' | 02:25:59
View 9 Replies
View Related
Oct 29, 2015
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
[code]....
View 1 Replies
View Related
