Datetime Variables
Mar 17, 2008
I have a datetime variable in an SQL table that is the date/time of the last data load. I read it from the table and store it in an SSIS variable. Then I select from the OLTP with a where claus greater than that variable as a parameter: SELECT Count(SalesLastChgAt) FROM Sales where SalesLastChgAt > ? .
If the count is zero there is no new data and the program terminates. If the count is greater than 0 I run the ETL. One of the first things I do is get the new MAX(SalesLastChgAt) and store it back in the table for use next time. It is being stored as 12-19-2007 16:42:23.290.
If I run the program with the parameter - which is shows up as 12/19/2007 4:42:23 PM - the program picks up records because the SalesLastChgAt date is apparently 290 thousandths of a second greater than the parameter. If I hard code '12-19-2007 16:42:23.290' no records are read.
This leads me to believe fractions of a second are being ignored. Is there a way to force SSIS to use fractions of a second? Or am I doing something else wrong.
Thanks.
View 12 Replies
ADVERTISEMENT
Feb 20, 2008
Sorry seem to be havign no end of trouble with SQL today. I need to create a query that will rank tickets using the row count of another table which has a relationship with the primarykey of that ticket table. I decided to spilt it into two querys, one which gets the count each ticketid has within a date peroid and relating to a os:: SELECT COUNT(*) AS count, Tickets.TicketID, Systems.OSFROM Rating INNER JOIN Tickets ON Rating.TicketID = Tickets.TicketID INNER JOIN Systems ON Tickets.SystemID = Systems.SystemIDWHERE (Rating.DateVoted >= @date)GROUP BY Tickets.TicketID, Systems.OSHAVING (Systems.OS = @osid) This works, BUT when I put in the date using: command.CommandText = "SELECT COUNT(*) AS count, Tickets.TicketID, Systems.OS FROM Rating INNER JOIN Tickets ON Rating.TicketID = Tickets.TicketID INNER JOIN Systems ON Tickets.SystemID = Systems.SystemID WHERE (Rating.DateVoted >=" + Datetime.Now.Adddays(-7) + ") GROUP BY Tickets.TicketID, Systems.OS HAVING (Systems.OS =" + 1 + ")"; And it throws up an error saying there was a syntax error at '22' (the hour I am currently on). So: Any idea how I can fix thisAny ideas on creating a query that will do the above, but also return the ticket informationHow do you link querys (I might have to do this if I can't work out 2)Please, If anyone has any ideas. This is driving me crazy
View 5 Replies
View Related
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Jul 9, 2007
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
View 3 Replies
View Related
Dec 6, 2006
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
View 1 Replies
View Related
Mar 14, 2007
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View 4 Replies
View Related
Mar 17, 2007
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View 2 Replies
View Related
May 13, 2006
Hi,
I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date
'31/08/2006 23:28:59'
I get the error "...datetime data type resulted in an out-of-range datetime value"
I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be?
cheers,
Ernest
View 2 Replies
View Related
Mar 11, 2014
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
View 1 Replies
View Related
Sep 7, 2007
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
View 5 Replies
View Related
Jan 17, 2008
e.g.
1st March 2005 12:00:00
is showing as
01/03/2005 00:00:00
instead of
01/03/2005
Why does this happen?
View 4 Replies
View Related
Jun 15, 2004
I have the following SQL:
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.
The SQL above returns
2004-04-20 00:00:00.000 Deposit ...
Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:
select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date
I get the following error:
Syntax error converting datetime from character string.
Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...
View 6 Replies
View Related
Jul 20, 2005
Hi,I have a text file that contains a date column. The text file will beimported to database in SQL 2000 server. After to be imported, I wantto convert the date column to date type.For ex. the text file look likeName dateSmith 20003112Jennifer 19991506It would be converted date column to ydm database in SQL 2000 server.In the table it should look like thisName DateSmith 2000.31.12Jennifer 1999.15.06Thanks in advance- Loi -
View 1 Replies
View Related
Nov 5, 2007
Hi,
I am trying to access a date column up to millisecond precession. So I cast date to as follows:
Code BlockCONVERT(varchar(23),CREATE_DATE,121)
I get millisecond part as a result of query but it€™s €œ000€?.
When I try to test the format by using getDate instead of DateTime column I get right milliseconds.
CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return
View 4 Replies
View Related
Sep 11, 2007
hi,
How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003
1/13/2007
01132004
1-1-2004
1.2.2001
there is no uniformity of the data that is stored currently.
thnkx in adv.
rahul jha
View 11 Replies
View Related
May 21, 2015
I have a table that has a unique ID and a datetime of when something changed.
See example:
IDÂ Â Â TimeStamp
16094Â Â Â 2013-11-25 11:46:38.357
16095Â Â Â 2013-11-25 11:46:38.430
16096Â Â Â 2013-11-25 11:46:38.713
16097Â Â Â 2013-11-25 11:46:38.717
16098Â Â Â 2013-11-25 11:46:38.780
[Code] ....
Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.
View 10 Replies
View Related
May 2, 2007
hi friends,
how to get the date of the first/last day of the week in sql...
can any one help me
Cheers,
raj
View 5 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Jan 28, 2008
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query?
View 5 Replies
View Related
Jun 9, 2008
Hi All,This is only my second day using C#/asp.net and English isn't my native language so I'll apologise in advance if this question doesn't make sense and/or is stupid . I'm trying to make a little event planner which was going well until I hit a small problem, the user needs to enter the event name and choose a date/time from dropdowns, this bit works okay... however I'm having difficulties with storing the date/time, at the moment I'm making a DateTime object and filling out the year/month/day/hour/minute selected on the form and adding that to the database field (which is a datetime field type).Problem is, I'm using the date format DD/MM/YYYY HH:MM:SS but the database stores it as MM/DD/YYYY HH:MM:SS so if I wanted to do things like:
select * from tbl where date > DateTime.Now - it isn't going to work because the month is switched with the day in the DB-versions.
Just to try and give another example, if I'm not being very clear, if I print DateTime.Now.ToString() it will display 10/06/2008 02:50:48 however if I put it into the database and pull it back out cast to a DateTime Object and then display that object via ToString() it'll be 06/10/2008 02:50:48
Any suggestions on what to do would be appreciated, usually in cases like this I'd just use the unix timestamp, but since C#/ASP.NET doesn't support that natively I was hoping there was an easier/native alternative. Also I don't have access to the server configuration so changing the localization of mssql isn't an option unfortunately.Thanks in advance!
View 7 Replies
View Related
Dec 14, 2005
After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.
System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.
Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.
At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.
anyone having this problem before?? hopefully you guys can help.
Thanks,
View 4 Replies
View Related
Apr 19, 2008
Advance thanks ....... My table is TimeSheet:----------------------------------- CREATE TABLE [dbo].[TimeSheet]( [autoid] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [starttime] [datetime] NOT NULL, [endtime] [datetime] NOT NULL, [summary] [nvarchar](50) NOT NULL, [description] [nvarchar](50) NULL, [dtOfEntry] [datetime] NOT NULL, [Cancelled] [bit] NULL) ON [PRIMARY] My Query is------------------ insert into timesheet (UserId, StartTime,EndTime, Summary, Description,DtOfEntry) values (2, '19/04/2008 2:05:06 PM', '19/04/2008 2:05:06 PM', '66', '6666','19/04/2008 2:05:06 PM')i m not able to insert value Error Message is-------------------------Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated. can any body give any solution
View 5 Replies
View Related
Aug 3, 2005
Hey, I have a big problem that i wanna search data from SQL by DateTime like thatselect * from test where recorddate='MyVariableWhichHoldDate'i use variable that holds Date info.i searched a lot infomation on net but there is no perfect solution. i know why this occur but there is no function to solve this problem. i used a lot of ways. it accept yyyy-mm-dd format but my variable format is dd-mm-yyyyy . is there any function for this problem? and any other solution.thanks for ur attentionregards
View 6 Replies
View Related
Jul 13, 2007
What is @@variables means in sql server?
View 3 Replies
View Related
Jan 21, 2004
Is there a way to use the LIKE keyword with variables like below?
DECLARE @Name CHAR(10)
SET @Name = 'MyName'
SELECT * FROM table
WHERE my_name LIKE @Name
This won't work, but you get the idea of what I want. Any thoughts?
Thanks,
View 1 Replies
View Related
Jun 4, 2004
Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?
View 7 Replies
View Related
Jun 8, 2005
Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
View 2 Replies
View Related
Feb 16, 2000
Hi,
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?
Thanks
View 1 Replies
View Related
Nov 2, 2000
I have this script:
.....
USE master
go
/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'
print @db1
print @dbf1
go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED)
go
.....
When I run it, I get the following errors:
.....
The name of this server is: KANSASCITY
KANSASCITYdatabase1
KANSASCITYdatabase1RL_log
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.
.....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH:
I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
Thank you (in advance)
View 2 Replies
View Related
Mar 28, 2003
Hi
I think this is an easy question.
Is it possible to use a variable in a create database statement
i.e.
declare @db_name varchar(20)
select @db_name='new_db'
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
DROP DATABASE @db_name
CREATE DATABASE [@db_name] ON (NAME = @db_name, FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataew_DB_Data.MDF' , SIZE = 300, FILEGROWTH = 10%) LOG ON (NAME = N'new_DB_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataImport_Utility_DB_Log.LDF' , SIZE = 30, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Also, if the above is possible, how can i pass a database name to the script if I am calling it from a batch file (using osql).
Thanks,
Jim
View 2 Replies
View Related
Jul 26, 2000
I want to change database in my SQL-script like this:
DECLARE @DB_NAME varchar(30)
SELECT * FROM @DB_NAME.dbo.TableName,
but it is syntactically incorrect.
I could use
USE (@DB_NAME)
but I write Stored Procedure, it is not allowed using
USE statement in any Stored Procedures.
View 2 Replies
View Related
Oct 31, 2005
Hey folks,
i'm trying to make a variable inside of a loop do this.
@a = 1
@b = Variable + @a
and then inside of the loop @a incriments by 1, so i'd get
variable1
variable2
...etc
but I can't get the variable to work right...
Any help would be great!
Caden
View 2 Replies
View Related
