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))
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.
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
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)?
Hi All
I'm getting an error that says that this can't be converted to an integer.
Here is the line that gets the error. dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()
I have also tried. dt = (DataView)(EventDataSource1.Select(dssa);
I am programming in VB
here is teh rest of my code.
Dim EventDataSource1 As New SqlDataSource()EventDataSource1.ConnectionString = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToStringDim dssa As New DataSourceSelectArguments()Dim EventID As String = ""Dim DataView = ""Dim dt As New Data.DataTableDim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)", conn)EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)")conn.Open()dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()EventID = dt.Rows(0)(0).ToString()EventDataSource1.SelectParameters.Add("@StartDate",StartDate)EventID = cmd.ExecuteScalar()
I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.-----------------------------------------------------DECLARE @TransactionID int <------ INSERT statement (after inserting the record, select the identity ID) ---------> Select @TransactionID = @@Identity RETURN------------------------------------------------------...Dim transactionID As Integerconnection.Open()Dim reader As SqlDataReaderreader = command.ExecuteReader(CommandBehavior.CloseConnection)Dim p3 As New SqlParameter("@TransactionID", SqlDbType.Int)p3.Direction = ParameterDirection.OutputtransactionID = Convert.ToInt16(p3.Value)connection.Close()...I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.I tried many different ways of conversion but couldn't resolve the error. Could anyone help?
View 3 Replies View RelatedHi folks,
I'm trying to import data from a text file (UnicodeData.txt) into
an SQL table. Some of the fields are unicode values (16 bits)
expressed as four hexidecimal digits. I've succeeded in importing
the data as character strings, but I have not found a way to convert
them into numbers. (They could be stored as int or nchar.)
I've tried convert(binary/int/whatever, string); E.g.
select convert(int, '0x1111') from import_unicode
gives the error
Syntax error converting the varchar value '0x1111' to a column of data type int.
I could write code to strip off one character at a time, convert the
hex digit to a decimal value, shift left, etc., but I find it hard
to believe that's the best way.
Any help is appreciated. Please email answer to lars_huttar@sil.org
as I don't read this board.
Thanks,
Lars Huttar
lars_huttar@sil.org
hi i want to insert the varchar value inter column, how can i convert the values and insert in to the record if i give direct column name i am getting a message
insert into EMP(id)
SELECT name from STU
i am getting a message like this
Syntax error converting the varchar value '200.00' to a column of data type int.
Hi,
I am extracting data from DB2 into SQL server using DTS. We have the data type char(5) in DB2 and int in SQL server.
I tried to give the
select integer(columname) as columname from tablename in data source.
I did parse the query it was Ok but If I tried to execute that giving the following error.
Can you give me your answere for this issue.
---------------------------
Package Error
---------------------------
Error Source : Microsoft OLE DB Provider for ODBC Drivers
Error Description : [StarQuest][StarSQL ODBC Driver][DB2]THE VALUE OF A CHARACTER STRING ARGUMENT WAS NOT ACCEPTABLE TO THE INTEGER FUNCTION
---------------------------
OK
---------------------------
In a table, I store an integer to represent a day of the week (1 = sunday ) and then in procedures, compare that to datepart(dw, getdate()) to do alternative tasks on certain days.
If I have the number 1, is there a built in function to convert that to sunday or should one just write a function to do just that?
HI!!!:shocked:
Im trying to convert 0x00085180F0A2D511B69600508BE96424 to Integer or numeric format.
I just tried, At to many forms and combinations of that query
Help me !!!
SELECT CAST(CAST(CAST("field name " AS nvarchar) AS varbinary) AS float)
select cast(cast("field name " as varbinary)as integer)
select convert(int," field name") from FILE
select convert(varchar," field name") from FILE
The only answer that I have is
-1947638748 or ‘ ‘ or
And if I try with to many rows of the field at the same format,
It Answer me the same: -1947638748 for all the rows.
Thank`s for all
SQL command ....
declare @found int;
set @found = 'select sid from StickyContent where StickyId='+0+' and UserId='+171
exec (@found)
delete from StickyContent where sid = @found
I need to pass this statement from my UI i.e in a single query. I am getting this error.
"Conversion failed when converting the varchar value 'select sid from StickyContent where StickyId=' to data type int.
"
I have this datetime:Â '2002-12-20 11:59:59'
I want to convert this to date (yyyy-mm-dd) to integer
This works fine for:Â SELECT CONVERT(INT, GETDATE())
But it doesn't work here:Â SELECT CONVERT(INT, '2002-12-20 11:59:59')
I want to convert date to integer without passing through any varchar conversion/result.
I want to convert a string into an interger so that my parameter can get one value, and have a seperate matrix list the value before the value selected.
My parameter is year. The user picks the year. And i want the crashcounts for the year displayed in the matrix. Then i have another matrix with a dataset similiar. I want this seperate Matrix/Dataset to display the previous year.
SO if the user selects 2004 from the dropdown. 2004 is displayed in the first matrix, and 2003 is displayed in the second matrix. The year attribute is in string format, and i cant change it in the cube. So i was told it could be converted in reporting services. with this
=CStr(CInt(Parameters!CrashStatisticalYear.Value)-1)
Question is, where do i put this...and how come when i put it in the parameters expression for the 2nd matrix, i get an error datatype messege? HELP !
I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.
example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70
as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.
Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.
Thanks
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?
I need to convert a column of data from alpha numeric to integer.
I am only querying the tables i.e. i don't have access to actually change the data tables themselves.
CAST or CONVERT throws up an error. Are there any other commands i can use at the query stage?
The data I need to convert is always actually a number. i.e. even though it is recognised as alpha numeric, the figure is a number.
I just need it to be converted to an integer so i can SUM it etc.
Iwant to convert the datetime to the integer value as shown within the brackets.
----2014-12-21 0:00:00 (1419091200000)
Hi there,
for displaying purposes I'm trying to convert number of months into years and months. ie. 35 months = 2 years and 11 months.
how can I do this within a T-SQL script?
Thanks for your help.
Manny
Hi,
I am querying DB2 from SSRS. I get an interger back that represents a time like this: HHMMSS
(The data type in DB2 is an integer.) I would like to make this representation of the time display a little more friendly. Does anyone have a good idea on how I can change the query or SSRS format to display semi-colons to break up the hours, minutes and seconds? Also, the other issue is that since I don't get leading zeros back....they probably need to be added to this value if the value is not a full 6 characters.
I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton
View 5 Replies View RelatedHiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies View RelatedI need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies View RelatedI'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.
year=2004
month=1
day=1
how to create a date from that?
date(year, month, day) does not seem to work.
Thanks.
Jeff
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
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.
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.
In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
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
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,