Informix Date Function Equivalent
Jul 23, 2005
Hello. I have an Informix SQL statement that I need to run in MS SQL
Server. When I try to execute it I get the following error message from
Query Analyzer:
Server: Msg 195, Level 15, State 10, Line 4
'date' is not a recognized function name.
Can anyone help me convert this informix sql statement into and MS Sql
Statement? Here is the query I have:
select
a.comp_code,
a.comp_date,
case when date(date(date(comp_date - day(comp_date) +1) - 2 units
month) - 1 units day) < b.inception_date then b.inception_date
else date(date(date(comp_date - day(comp_date) +1) - 2 units month) - 1
units day)
end prior_date,
a.net_return,
a.net_uv,
a.gross_return,
a.gross_uv,
a.estimated_flag
from composite_perf a, composite_detail b
where
(month(a.comp_date) in (3,6,9,12) or a.comp_date = b.inception_date)
and a.comp_code = 'AEU'
and a.comp_code = b.comp_code
into #tmp_composite_data
Thanks in advance.
View 4 Replies
ADVERTISEMENT
Jul 20, 2005
Hi All,I am facing a problem with a sql what i used in MS Access but its notreturning the same result in MS Sql Server 2000. Here i am giving thesql:SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHEREMY_ID=1The above sql in ACCESS return me the date in below format in onecolumn:Friday 09, 2003But in Sql server 2000 i am not getting the same format eventhough iam using convert function, date part function etc.Please if you find the solution would be helpful for me..ThanksHoque
View 3 Replies
View Related
Apr 28, 2004
Hi all. Informix and DB2 support something called synonyms that allow you to basically create sort of an alias for a table at the database level. Think of it sort of as a shortcut or link to a table. Does SQL Server 2000 have a similar ability and if so how?
I know someone will ask why you want to do this, so heres a quick example:
If you have one legacy application that expects to write to one particualr table, but you wish to partition that table across several tables, you can break table1 up into tablea,tableb,tablec and then create a synonym called table1 that would point to only the appropriate table at the appropriate time. This way you can break a huge HUGE table up into logically discreet smaller tables and manage the creation of the appropriate synonym in some wrapper that sits in front of the legacy application...thus allowing you to retool a table that has outgrown its original design without having to crack open dreaded legacy code.
So, anyone?
View 1 Replies
View Related
Nov 16, 2006
...For Oracles DECODE function?
I am trying to get a conditional output RETURNed to the Grid output and have not found it in SQL Help. Help!!!
Here is Oracles example:
Select Distinct City,
DECODE (City, 'Cincinnati', 'Queen City', 'New York', 'Big Apple', 'Chicago',
'City of Broad Shoulders', City) AS Nickname
From Cities;
View 4 Replies
View Related
Jan 21, 2005
Hi,
I'd like to perform an update on a database only when certain conditions are met. Hence, one of those conditions would be a positive answer from the user running the query. Basically, I'd like to display a message with a yes/no option for the user to choose from. If and only if the user user selects the YES option should we perform the update.
Inside a transact SQL "IF ... THEN", how can I achieve this?
Thanks,
SC
View 10 Replies
View Related
Mar 8, 2005
what is the equivalent of val() function in SQL server?
and cstr() function
View 4 Replies
View Related
Dec 3, 2007
Trying to write the below in an expression box, I still have not figured out an equivalent to IN? in SQL syntax, so far Ive had to use OR? to get it to work. Anyone know the right way? Im trying to avoid writing this in the stored proc
Example:
Iif (Fields!INVESTOR_NBR.Value in (0, 265, 465), "DIRECT", "PARTICIPATIONS")
View 1 Replies
View Related
Jan 31, 2008
Hi,
I have successfully created a CLR function (C#) in SQL 2005 and call it from a stored procedure. I need to be able to provide the same functionality for SQL 2000 servers. Is this at all possible? I have read loads of conflicting information on the web about using COM to access the dll can it be done or not? The function accepts serveral input parameters and internally access the data store using a context connection and a datareader.
Thanks.
View 6 Replies
View Related
Apr 13, 2004
I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)
My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.
The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)
Any help is greatly appreciated!
JT
The goodness we share is rewarded twice!
View 10 Replies
View Related
Oct 14, 2005
Hi All,
Once again I find myself smacking my head into a wall (and it will probably be for naught). I'm trying to find a specific character in a string within a specific record. I've tried substring, but it's not quite getting me where I want to be. I even tried "nesting" the substring and got nowhere (but I could have had the syntax wrong when I nested it).
Here is a sample
Select substring(productimagefile, 20, 5)
from my table
What I need is something like this:
Select substring(findoneof(productimagefile, _r, 1), 1, 5)
from my table
Actually, on the start of the substring, I need it to be where it finds the _r, just not sure how to tell it that.
The replace function is the closest I've seen to what I need, but I don't want to replace it with anything, I just need to find it in the string.
So, if anyone has any ideas, I'd be grateful (and my head would stop hurting....)
Schimelcat
View 1 Replies
View Related
May 5, 2006
Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
View 14 Replies
View Related
May 5, 2006
Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only
one character or a sequence of character and not each occurrence of
each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
View 4 Replies
View Related
Apr 10, 2006
I'm happyguy and this is the first time to post my problem. Thanks a lot
here is a SQL query that I know how to write in Oracle, which is
Select * from AttandanceRecord where date like '%__/Apr/2006%'
but, now i'm developing my system with using Ms Sql Server 2000, so I don't know how to write a query with same output as above...
Please, can somebody help me??
View 6 Replies
View Related
Oct 17, 2007
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
View 1 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
βMsg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.β
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Mar 10, 2008
I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
View 4 Replies
View Related
Mar 3, 2005
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
View 7 Replies
View Related
Feb 19, 2007
hi experts,
i'm working in a web page for some statistics and i have a calendar where the customer can choose a day a week or a month and according to the date he select i need to query the database according to the date selected.
what i want to know is how can i store the date for a day in a variable so i can call it from a stored procedure, the day actually is easy what i want is how can i store the whole week in a variable so i can give it to the stored procedure and query the data in the database according to the whole week may be with startday and endday
also the same problem for the whole month, any idea how can i implement that in C#?!!!
thanks
View 14 Replies
View Related
Apr 9, 2001
I need to format the getdate() function in SQL to return only the date and year and not the timestamp.
View 2 Replies
View Related
Feb 26, 2002
Is there any function avaibale that separates the DATE and TIME from one column having DATETIME in the following format:
2002-02-02 07:33:59.000
Any suggestion is highly appreciated.
Viv
View 2 Replies
View Related
Oct 28, 2004
I am running a query using the MS Query Analyzer. I am inserting a large number of fields into a table, one of the fields is a date field where I enter the date (Hard Coded) that the query is run. I was wondering if it was possible to have the script get the date while it is running via the Date() function. I have tried the following:
Declare @DateVar SmallDateTime
Set @DateVar = Date()
I am getting an error on the second line, so I guess the Date() function is not native to SQL. So is there another way of accomplishing this?
Thanks
View 2 Replies
View Related
May 26, 2004
Is it possible to retrive the number of days between two dates.
I'm aware of the DateDiff function but I can't figure out how to calculate the number days between two columns containing dates in a table.
All help is welcome
Regrads OIS
View 4 Replies
View Related
Jun 5, 2008
Dear gurus,
How do get Saturdays & sundays in between the given dates.
Thanks in advance
cool...,
View 4 Replies
View Related
Feb 17, 2006
I'm trying to create a function that takes a DATETIME field and adds 30 days to it. Does anyone know the syntax for this? Thanks in advance. :)
View 2 Replies
View Related
Apr 20, 2006
Hi all, it's me again.
I'm trying to implement a query that will show all records from three months prior to a certain date (that the user will input) and that date.
This is what I wrote:
SELECT Transactions.Date, Transactions.Details,
FROM Transactions
WHERE Transactions.DATE Between [Forms]![FormNAME]![Date] And DateAdd("M",-3,[Forms]![FormNAME]![Date]);
The idea is that the user is presented with a form which has a single text box to input the date. Then he/she clicks a button (which runs the above query) and the list is presented.
However, when I try to run it, I get back ALL records before a certain date...not just for the three months prior to it.
What am I doing wrong?
View 8 Replies
View Related
Jun 8, 2006
HOW TO CONVERT THE VALUES IN DATETIME COLUMN FOR EX
2006-03-17 03:56:00.000
TO
2006-03-17 00:00:00.000
I HAVE TO COMPARE ONLY DATE TO EXTRACT REPORTS.
THANKS IN ADV
View 6 Replies
View Related
May 2, 2007
Dear Experts,
Actually, ineed a function to display wether the given date is working day or not............
id the given date is a sunday or saturday, then it should display like...holiday.
or else it should say working day..
please help me in this regard.......
one more thing, if the given date is sunday, then it should give the previous sunday date (minus seven days)
thank you in advance......
I'm trying with this function
CREATE FUNCTION ISOweek (@DATE datetime)
RETURNS int
AS
BEGIN
DECLARE @ISOweek int
SET @ISOweek= DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0)
SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm,@DATE)=12) AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET @ISOweek=1
RETURN(@ISOweek)
END
Vinod
View 1 Replies
View Related
May 8, 2007
I need to sort some data by date: my date format is looks like this
5/7/2007 11:38:54 AM. but i need to sort sort by just the first part "5/7/2007" how do i achieve this.?
Melvin Felicien
IT Manager
DCG Properties Limited
View 9 Replies
View Related
Jan 23, 2008
i have just started working on SQL and i am trying to solve this puzzle maybe smoe one could do it.. i have to make a query such that
it will take all the transactions throughout the day sort them with the accnt number and then give total amount in the transaction, also the total amount should be > 10000 if some one can try and locate the problem with hte logic i would appreciate it..
just for instance what i did was i tried using the convert in group by and then sum(deposit) this doesnt work.. i am really confused please help.
i have written this query:
declare @datelastweek as datetime
declare @yesterday as datetime
declare @date as datetime
set @Date = Convert(varchar,GetDate() - 1,1)
SET @datelastweek = DATEADD(Day, 1, (DATEADD(Week, -1, @Date)))
SET @yesterday = dateadd(day,1,(dateadd(day,-1,@date)))
select accountno, sum(amount), convert (varchar, TransactionTime -1,1)
from deposit tb join transaction t on tb.id=t.id
where TxnTime between @datelastweek and @date
group by DATEADD(d,DATEDIFF(d,0,TxnTime),0),t.Accountno, txntime
having sum(tb.amount)>10000
Rahul
View 12 Replies
View Related
Mar 4, 2008
i have seen that date function which MVJ has created and its really very useful..
but i m getting error while selecting getdate() as @last_date in that function
SELECT
distinct date FROM
dbo.F_TABLE_DATE('1/1/1999', getdate()) AS Date
error is : Incorrect syntax near 'getdate'
what's mistake?
can anyone help me?
thanks in advance.
View 6 Replies
View Related
Mar 17, 2008
hai to all,
i need a help ,that if i give the UTCdate as parameter in my stored proc i have to get the output as getdate() likewise if i give the getdate() i have to get the o/p as Getutcdate()
Thanks in Advance
View 2 Replies
View Related
Feb 7, 2007
Hi!I have a report that needs to be run on the seventh of every month forthe dates from 6th of the previous months to the 5th of the currentmonth. For example, I have to run a report on February 7th for the01/06/2007 to 02/05/2007. Right now I am doing it manually but I wascurious if there a function or something that will give me therequired date range on the 7th of every month.Any ideas?Thanks,T.
View 1 Replies
View Related
