CASTING Statement For A Date
Sep 6, 2006
I am selecting older legacy data from an AS400 mainframe that we still use. I am fairly new to constructing T-SQL statements so I hope I am doing this correctly. There is a table from the AS400 that has been setup with a field for TMONTH, TDAY, and TYEAR. Instead of having one field for a date, for some reason years ago this was set up this way.
I now have this statement in my SELECT statement and it is not working:
WHERE (CAST(OWNR.TMONTH + '/' + OWNR.TDAY + '/' + OWNR.TYEAR AS DATETIME) >= @startdate)
I am not getting a syntax error, however I am getting "Error Converting data type varchar to numeric. These fields on the AS400 are set up as numeric fields.
What do I need to do differently? Should I use a CONVERT instead and if so, how would I structure that statement.
Thanks for the help
View 3 Replies
ADVERTISEMENT
Feb 18, 2008
Hi All
My brain has truned to mush at the moment.
:(
I want to sort on a char(5) column with data in it that looks like
01/07
05/02
06/01
12/01
07/98
so it comes out in the human date order.
e.g
07/98
06/01
12/01
05/02
01/07
--
David
View 6 Replies
View Related
Apr 28, 2004
I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.
ddave
SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
END
View 7 Replies
View Related
Oct 24, 2007
I have a Foreach loop container that I'm using to loop through a directory and copy files to a new directory. The file name contains the date and I need to compare the value to the current date to determine if the file should moved. I'm having a problem converting the string to a date.
I have a FileName variable of type string that is in this form: 200710221200_FileName_7001.log
I have a FileDate variable of type datetime that I'm trying to use the following expression on:
(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) +"-"+ SUBSTRING(@[User::FileName] , 5,2) +"-"+SUBSTRING( @[User::FileName] , 7,2))
Which is giving me the following error:
The expression "(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) +"-"+ SUBSTRING(@[User::FileName] , 5,2) +"-"+SUBSTRING( @[User::FileName] , 7,2))" has a result type of "DT_DBDATE", which cannot be converted to a supported type.
From the help files, it appears to me that the DT_DBDATE cast uses the form of yyyy-mm-dd. I'm not sure what I'm doing wrong. Any help is greatly appreciated.
Thanks!
Wendy Schuman
View 12 Replies
View Related
Jul 21, 2014
My current Query takes the DATE value stored in P.CreatedDate and makes it VARCHAR, then stores it in the table. I need to format this to return YYYYMMDD. How would I go about this?
Current Code:
Code:
CAST(P.CreatedDate AS VARCHAR) AS DateEntered,
View 3 Replies
View Related
Sep 9, 2015
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)
Note: Both col1 columns are of type nvarchar
View 6 Replies
View Related
Jul 23, 2005
Does anybody know how could I calculate the new date(adding @c to @bor subtracting @b to @c), having for example a declaration like this:DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @b='3.04.04';SET @c=6and to calculate the number of days between two dates with this kindof declaration(@a-@b or @b - @a):DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @a='12.2.04';SET @b='3.04.04';Thanks in advance.
View 1 Replies
View Related
Sep 21, 2006
HiI 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 Related
Aug 25, 2004
Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.
Thanks
Sam "O"
cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol)
SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat,
(cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) +
cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) +
cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) +
cast(sat as numeric(4,2))) as totcol from
" & strTableName, conTimeCard)
intUpdateCount = cmdInsert.ExecuteNonQuery()
View 4 Replies
View Related
Jul 3, 2007
Hello,
I'm new to SQL Server and I have to finish a VB.Net program from an employee that has left the company.
I have a table with column 'vanafdatum' witch has datatype char but they stored only dates in this column ( like this: 13/09/2006).
When I try to run the next querie I receive an error message "The conversion of a char data type to a datetime data type resulted in an out of range datetime value".
This is the querie I try to run
SELECT MAX(CAST(vanafdatum AS datetime)) AS vanaf_datum
FROM tarieven
WHERE (vanafdatum <
(SELECT getdate()))
Is there any way I can cast the char datatype to a datetime datatype without getting this error.
I allready tried to change the data type from char to datetime, but doesn't work either.
A possible solution is to erase the column an manually fill in all the fields again in the data type datetime, but that seems a little stupid to do.
So I wonder if there is a solution that can change the data type for all rows in the database without losing any data.
View 5 Replies
View Related
Aug 30, 2006
I've got an ftp task that will be downloading a couple of files each night. today they're called
blah20060830blah
the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.
Thank you
View 23 Replies
View Related
Feb 6, 2007
Hi,
I would like to know if casting is supported in SQL Server Compact Edition. I get an error when i attempt to cast a datetime or bigint value to nvarchar or ntext. CAST(datetime AS nvarchar(15))
Does anybody knows if its supported or how could i cast values?
Thank you
View 1 Replies
View Related
Oct 10, 2005
Hi,I've come across a problem that requires i cast a string to a date, had a go but can't get it to work properly.SELECT EventID, EventName, EventShortDesc, EventLink, EventStartDateFROM dbo.tbl_EventWHERE (CAST EventStartDate AS DATE) > GetDate()ORDER BY EventStartDate DESCBasically i only want events which haven't expired (were before the current date) to be returned. EventStartDate is held as an varchar(10) therefore needs to be cast before i can compare. Is this the best way or is there a better one? If it is how do i get it to work?Any help would be great thanks,drazic19
View 2 Replies
View Related
Nov 13, 2007
Hi all,
I'm trying Insert data using INSERT INTO kind of like below
INSERT INTO table1(
col1
col2
col3
col4)
SELECT
col1
col2
col3
col4
FROM
Server.DB.table2
say, col3 from table 1 is numeric (28,8) and col3 from table 2 is float.
how do I cast this so it works??
thank you!
View 9 Replies
View Related
Apr 6, 2006
Just a question about multi casting.
If I create a copy of a data set using a multi cast, do operations on one of the output sets effect the other output set?
For example, in one ouput set I'm setting a column to NULL - this is actually updating the other set as well. Is this meant to happen?
Thanks!!
View 1 Replies
View Related
Jun 18, 2007
Can anyone see what's wrong with this transformation?
((DT_STR)([IN-DLN]))
I'm trying to cast DLN as a string, but I keep getting a parsing error in the derived column tranformer...
Thanks!
Jim Work
View 7 Replies
View Related
May 12, 2006
Hello,
When I declare a VB variable Dim s as Decimal,
I want to cast d like this : 1452,4141,0045,47756544,04
Only with to digits after the ","
How can I perform this
View 5 Replies
View Related
Feb 25, 2005
Can anyone please telkl me what is wrong with this portion of a SQL statement. I have been racking my brain over this and can't seem to get it right...
(CASE playerstats.fgm WHEN 0 THEN 0 ELSE (cast(100.00 * ((cast(SUM(playerstats.fgm)) as Decimal(8,2))/(cast(SUM(playerstats.fga)) as Decimal(8,2)))) as decimal(8,1))) AS fgp
I had it working fine, but when playerstats.fgm was a 0 then I got a divide by 0 error. This was the code when it was working ok as long as no one entered a 0 for fgm
(cast(100.00 * (cast(SUM(playerstats.fgm) as Decimal(8,2))/cast(SUM(playerstats.fga) as Decimal(8,2))) as decimal(8,1))) AS fgp
All I am trying to do is find a percentage... when playerstats.fgm = 0 then the percentage will be 0.
Any help will be much appreciated!!! :confused:
View 1 Replies
View Related
Sep 28, 2006
If I have an SQL query which returns an aggregate of several decimal fieldslike so:(sum(COALESCE(myDecimal1, 0)+sum(COALESCE(myDecimal2, 0)+sum(COALESCE(myDecimal3, 0)) as MyTotalI get an rounded integer in MyTotal.However, if I do the following:sum(COALESCE(myDecimal1, 0)+COALESCE(myDecimal1, 0)+COALESCE(myDecimal1, 0)) as MyTotalI get a (proper) decimal value.Does anyone know why the first case returns an Integer?- Don
View 4 Replies
View Related
Jun 10, 2015
Biz talk server executes every query twice, first with FMT only to get metadata.If metadata changed, it will produce error.Now, i have query like:
SELECT col1, col2, col3 INTO #tmp FROM dbo.myTable
How SQL decide the type of column in #tmp? Does it create column of the same type as it is in dbo.myTable or its decision is based on result?For example, col1 is varchar(20), but col1 in #tmp will be varchar(200) or even nVarChar.Col2 is INT, but result is for example low number(like, 1,2,3,) - is it possible that col2 in #temp table will be smallint or tinyint even if col2 in myTable is INT.If that is true one way to overcome that will be to explicitly CAST all columns to proper data type. Is there any performance impact of casting: CAST(col1 as VARCHAR(20)) if col1 is already varchar(20) in dbo.myTable?
View 9 Replies
View Related
Jan 7, 2008
I am trying to Convert integer into float value by using
CAST(value AS float).
But seems it doesn't work. I also used
CONVERT(float, value)
but it is alos not working properly.
Any Idea?
View 5 Replies
View Related
Oct 10, 2006
Is there a way do disable/disallow implicit casting in Sql Server 2000?
Say for example:
if ' ' = 0 select getdate()
This wil print out the current date and time.
But ' ' (a string, varchar, whatever) is not the same type as 0 (say, int). Implicit casting is nice, but is there also an off switch?
View 2 Replies
View Related
Jun 29, 2007
Hi all,
I am running into a problem which seems to indicate SQL Server 2005 is pretty aggressive with its implicit conversion which makes UNPIVOT apparently unusable for my current task of creating attribute/value pairs of strings for each of the records unpivoted around the primary key.
WITH cte as
(
SELECT 1 as Code, '2' as Status, 'Some Guy' as CreatedBy, Convert(varchar(19), getdate(), 120) as CreatedDate
)
SELECT
[Code]
,[Attribute]
,[Value]
FROM
cte
UNPIVOT([Attribute] FOR [Value] IN ([Status],[CreatedBy],[CreatedDate])) as U;
Msg 8167, Level 16, State 1, Line 1
The type of column "CreatedBy" conflicts with the type of other columns specified in the UNPIVOT list.
The same result shows up if I use an inner SELECT instead of the CTE:
SELECT
[Code]
,[Attribute]
,[Value]
FROM
(
SELECT 1 as Code, '2' as Status, 'Some Guy' as CreatedBy, Convert(varchar(19), getdate(), 120) as CreatedDate
) cte
UNPIVOT([Attribute] FOR [Value] IN ([Status],[CreatedBy],[CreatedDate])) as U;
It seems to me that some arbitrary decision about what column is processed first is made and the process fails as soon as a column is met which is not of the same datatype. I also think it is interesting that the engine will implicitly cast from varchar to DateTime but not the other way around.
Any thoughts on how I can get around this?
Thanks in advance,
Calvin
View 1 Replies
View Related
Apr 27, 2001
If I used the following syntax, I get 0 rows but I know I have records that were modified a year ago from current date.
c2.lastdate=getdate()-365
What is the syntax I should use to return rows based on the current date?
Any ideas?
Valeria
View 2 Replies
View Related
Apr 21, 2006
Here is my expression in a derived component:
"Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO
PROP_NO comes from ms sql server , and the derived component datatype for this column is DT_WSTR.
The destination will be ms sql server, and i have a data conversion after the derived component to cast from DT_WSTR to DT_STR.
However, the derived component failed everytime giving me
Error: 0xC0049064 at Load Ponl_Well, Derived Column [1342]: An error occurred while attempting to perform a type cast.
Anyone know how i can eliminate the data conversion component and just do my string and column concatenation in the derived column and have it output as DT_STR?
View 3 Replies
View Related
Jul 5, 2007
Hello, all.
Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?
(DT_UI4) (SUBSTRING(@[User::FullDataPath], LEN(@[User:ataPath]) + 1, 2))
I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:somethingsomething2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.
Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.
Thanks for any help.
View 7 Replies
View Related
Apr 16, 2007
Hi.
Current development setup: Visual Studio 2005 (C# language), SQL Server 2005 Express Edition.
I am at this point changing some of my application. It stores a lot of information for times of trains. I decode a file which is text based and my times are stored in varchar(4) columns with the format "HHmm".
I am now at a stage where I need to add values to these times and perform a search based on two time values (a minimum time and maximum time).
I really need to be able to add another varchar(4) value to the times stored in my database (also varchar(4) - may look at this again in future). I have absolutely no interest in storing seconds, modifying the database to include the time colon, nor am I interested in the date portion of a time string, only the 4 digits representing the hours and minutes.
Can this be done easily in SQL server? For example:
varchar(4) of 0445 + varchar(4) of 0027 should result in the time 0512.
Similarly a varchar(4) of 2358 + varchar(4) of 0015 should result in the time 0013.
This has to be very quick to execute.
Thanks for any help you can offer!
Sean
View 1 Replies
View Related
Nov 30, 2007
Hi Guys,
I have a table "HOLIDAY" with column "HOLIDAYSTART" and "HOLIDAYEND" in date/time type.User can apply new holiday on a form with value "STARTDATE" and "ENDDATE", but how do I check if the new holiday dates already exist in the table??
I looked around and found you can use something similar to following to check for single date,select * from holiday where ('1-12-2007' >= HOLIDAYSTART) AND ('1-12-2007' <= HOLIDAYEND)
but how would I do it with my situation above with between 2 dates? any help? Thanks in advance.
View 3 Replies
View Related
Jul 20, 2005
Hi,I'm trying to run a select statement that takes includes an if/elseclause. I need to select the 'tran_date' between....if the current month is greater than 10 i.e. after OCT then thetran_date will be between '01-Oct' - plus current year or thetran_date is '01-Oct' plus previous year.and the current dateHere is my script so far:SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,b.description PROJECT_CODE, sysdateFROM actrans a, acactivity b, dual cWHERE a.resource_type = 'E'AND a.acct_category = 'TIME'and a.activity like '9-WW-357852%'and b.activity = a.activityand tran_date betweenif to_char(sysdate, 'MM') > 10)begina.tran_date = '01-Oct' & to_char(sysdate, 'YY')endelsebegina.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))EndORDER BY tran_dateCan anyone help me out with this? I need to run it as a job, but notsure whether I should be using a stored procedure etc..Thanks in advance.George
View 1 Replies
View Related
Sep 8, 2007
A SQL Server 2005 db has three date related-columns (MonthGiven,
DayGiven, YearGiven) each as smallint datatype. I would like to
create a DocDate column (datetime datatype) that combines the data from
the three existing date-related columns. I have tried casting and
simple concatentation without success. ALTER TABLE Details ADD DocDate DateTime NULL
UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime)
I think I need to be doing a Conversion instead of casting but
have been unable to implement info I have found in the SQL Server
Developer Center in my situation.
View 11 Replies
View Related
Aug 10, 2007
Hello,
I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?
Thanks!
View 4 Replies
View Related
Sep 24, 2015
Is there any way to convert a bind variable, which is a list of integer or string values, to nested table in MS SQL Server. I am looking for something like
CAST in Oracle, which converts a varray type column into a nested table. Do we have something like this in SQL Server.
in Oracle:
SELECT CAST(s.addresses AS address_book_t)
FROM states s
WHERE s.state_id = 111;
I have read about Table valued Parameter, but I am looking for another solution.
View 4 Replies
View Related
Mar 5, 2008
I would expect this query:
select
case when cast(DT as int) = cast(cast(DT as float) as int)
then 'Consistent'
else 'Inconsistent'
end
from (select cast('27 Jan 2008 13:00' as datetime) as DT) X
to always return 'Consistent'. My expectation is not satisfied; the question is, is it unfounded?
View 13 Replies
View Related
