|
|
|
|
Hello,
Can any one tell me common function for SQL server and Oracle for getting month from the given date.
For Example,
In SQL Server
--> CAST(DATEPART(Month , Booking.BookingDtTime) AS VARCHAR(20)) AS Month_Value
In Oracle
--> TO_CHAR(Booking.BookingDtTime, 'Month') AS Month_Value
I want a common function for the same which works both in SQL Server as well Oracle.
Thanks..
|
|
|
|
|
NTheOne wrote: I want a common function for the same which works both in SQL Server as well Oracle.
Good luck with that. They are different databases using different sql dialects (TSQL and PLSQL) what makes you thinks there is or should be a common function in each dialect. You are going to have to code around the differences and detect the type of database you are working against.
[edit]
One way would be to code your own function in each database and use the same name. Functions are on a roll this morning.
[/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You will encounter many more differences between SQL Server's SQL and Oracle's SQL. When I made our application work with both of them, I created a framework for that. The "S" in SQL stands for "structured", not for "standard"!
|
|
|
|
|
There's ANSI-92 SQL, and Sql Server supports that. Doesn't Oracle have something similar?
I are Troll 
|
|
|
|
|
Why?
I would suspect there isn't one.
If you are attempting SQL independence that means you can't use stored procs and must be using another language to wrap it, so just use that language to extract the month.
If using in a where clause then construct an appropriate timestamp value that represents a month range. This has the advantage that it is probably faster as well.
|
|
|
|
|
Hi Everyone,
First: what is desired
Most of us probably know this trick for clipping of the time part of a DATETIME value (at least you can find it in an awful lot of places on the internet):
@Date = DATEADD (DD, DATEDIFF (DD, 0, @Date), 0)
It will simply get you a new DATETIME value with the time set at 00:00:00.
If you use any other datepart abbreviation you get the same but with the date or time clipped at whatever you selected.
Example: if you use MI you get your DATETIME clipped at the minute value (i.e: seconds and milliseconds set to 0).
It is very useful but it would be even more useful if it were possible to put this in a function.
Then it would be possible to call "my_f_cliptime (XXX, DateTimeValue)" in which XXX (note the lack of quotes) would be the datepart abbreviation you want to pass to the function for further use within the DATEADD and DATEDIFF operations.
The problem is that DATEADD and DATEDIFF do support datepart abbreviations but you have to include them in your source code directly.
Also: you can't pass those abbreviation as strings because DATEADD and DATEDIFF do not accept them that way.
So here is the question: does anyone know of a way to pass the datepart abbreviations (there does not seem to be a datepart variable type) or, alternatively, a way to convert equivalent strings to datepart abbreviations before passing them to DATEADD and DATEDIFF ?
For the moment I suppose there is'nt but then again you never know.
Bye
PS: I am generally not very curious but if possible I prefer knowing everything
|
|
|
|
|
According to This SO answer[^] there is no way to paramterize this argument, so you're stuck with CASE logic
Something like this would do nicely:
CREATE FUNCTION dbo.FlooredDate
(
@date DATETIME,
@interval VARCHAR(10)
)
RETURNS DATETIME
AS
BEGIN
RETURN CASE @interval
WHEN 'DD' THEN DATEADD (DD, DATEDIFF (DD, 0, @Date), 0)
WHEN 'MI' THEN DATEADD (MI, DATEDIFF (MI, 0, @Date), 0)
END
END
GO
|
|
|
|
|
Too bad, I did not see this particular answer before but it confirms a number of similar others I have seen.
I knew I could do it with a case structure like that but I wanted to do it with less code if it was possible.
It just goes to show that you can easily want it all but getting it all is a lot tougher.
|
|
|
|
|
Turn the kludge into a UDF to hide the bulk code and then you have a nice neat function to call. Especially as this feels like it should be reusable code.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
errr... thats exactly what my example had.
|
|
|
|
|
Then you sir deserve 5, I was responding to his too much code comment  without actually registering the details of your response otherwise I'd have given credit where it was due!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why, thank you 
|
|
|
|
|
Filip Dossche wrote: but I wanted to do it with less code if it was possible
Just to be clear (it wasn;t explicit from my original post), that I had shown the code for a User Defined Function (UDF), and therefore this code would be written once, and not repeated every time you needed this logic.
Thereafter, the usage would be as simple as
SELECT * FROM Whatever WHERE date>dbo.FloorDate('DD',GetDate())
or perhaps
INSERT INTO somewhere (someInfo,someDate) VALUES ('abc',dbo.FloorDate('DD',GetDate()))
|
|
|
|
|
CREATE FUNCTION [Lib].[DateTruncate] (@Subject DateTime , @Granularity varchar(4))
RETURNS DateTime AS
BEGIN
DECLARE @DW int
DECLARE @DF int
SET @DW = DatePart(dw,@Subject) - 1
IF @Granularity = 'YY'
BEGIN
SET @Subject = dateadd(mm,-datepart(mm,@Subject)+1,@Subject)
SET @Granularity = 'MM'
END
IF @Granularity = 'MM'
BEGIN
SET @Subject = dateadd(dd,-datepart(dd,@Subject)+1,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'SU'
BEGIN
SET @DF = 7-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'MO'
BEGIN
SET @DF = 1-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'TU'
BEGIN
SET @DF = 2-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'WE'
BEGIN
SET @DF = 3-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'TH'
BEGIN
SET @DF = 4-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'FR'
BEGIN
SET @DF = 5-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'SA'
BEGIN
SET @DF = 6-@DW
IF @DF >= 0 SET @DF = @DF-7
SET @Subject = dateadd(dw,@DF,@Subject)
SET @Granularity = 'DD'
END
IF @Granularity = 'DD'
BEGIN
SET @Subject = dateadd(hh,-datepart(hh,@Subject),@Subject)
SET @Granularity = 'HH'
END
IF @Granularity = 'HH'
BEGIN
SET @Subject = dateadd(mi,-datepart(mi,@Subject),@Subject)
SET @Granularity = 'MI'
END
IF @Granularity = 'MI'
BEGIN
SET @Subject = dateadd(ss,-datepart(ss,@Subject),@Subject)
SET @Granularity = 'SS'
END
IF @Granularity = 'SS'
BEGIN
SET @Subject = dateadd(ms,-datepart(ms,@Subject),@Subject)
END
RETURN @Subject
END
(Hmmm... I thought it did half-hours and quarter-hours too. That must be in the C# version.)
|
|
|
|
|
The OP was looking for less code. 
|
|
|
|
|
I don't know that language.
|
|
|
|
|
You can pass in with a text literal and then use dynamic SQL to invoke it.
Probably less performant, perhaps less safe, but with less code.
|
|
|
|
|
Hi
Is it possible to use one table more than once when creating a SQL VIEW? For example, the tables I`m using store data about houses and it's features (e.g. bathrooms, bedrooms, etc), and what I want to accomplish is to display the number of bathrooms as well as the number of bedrooms in one view. To do this I`m using the COUNT function to count the number of FeatureId's where the FeatureName = 'Bathroom' etc. as shown below:
SELECT DISTINCT dbo.Mandate.Id, COUNT(BedroomFeature.FeatureId) AS Bedrooms, dbo.MandateType.MandateType, dbo.Mandate.ErfSize
FROM dbo.Mandate INNER JOIN
dbo.MandateType ON dbo.Mandate.MandateTypeId = dbo.MandateType.Id LEFT OUTER JOIN
dbo.MandateListing ON dbo.Mandate.Id = dbo.MandateListing.MandateId LEFT OUTER JOIN
dbo.MandateFeature AS BedroomFeature ON dbo.Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.Feature AS Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.Feature ON BedroomFeature.FeatureId = dbo.Feature.Id
GROUP BY dbo.Mandate.Id, dbo.MandateType.MandateType, dbo.Mandate.ErfSize
This displays perfectly with the number of bedrooms. How can I show an extra column for the number of bathrooms? I tried adding the features table again and giving it a different alias, but somehow that didn't give me the desired results
|
|
|
|
|
One method i can think of is using a store procedure to get your count separately.
Or a sub query in the select parameter.
But either ways aren't really efficient ways.
P.S. first time answering questions here. Hoped I helped
|
|
|
|
|
Could you give a bit more detail on the structure of the database, as it seems to me this is a really difficult way to find out the number of bathrooms and bedrooms for a particular house, especially for a database that profiles houses.
...and I have extensive experience writing computer code, including OIC, BTW, BRB, IMHO, LMAO, ROFL, TTYL.....
|
|
|
|
|
I have a MandateFeature table that contains two Id's , MandateId and FeatureId. Each of these are foreign keys from two different tables (Feature and Mandate). If a mandate (house) contains 3 bedrooms and the Id for bedroom is 5, then the entries in the MandateFeature table will look something like this:
MandateId | FeatureId
----------------------
2, 5
2, 5
2, 5
If a mandate (house) has 2 bathrooms and the Id for bathroom is 6, then the table would look something like this:
MandateId | FeatureId
----------------------
2, 5
2, 5
2, 5
2, 6
2, 6
What I want to do, is to count the number of bedrooms as well as the number of bathrooms and then display it as follows:
Bedrooms: 3
Bathroom: 2
So I get it working using this:
SELECT DISTINCT dbo.__Mandate.Id, COUNT(BedroomFeature.FeatureId) AS Bedrooms, dbo.__MandateType.MandateType, dbo.__Mandate.ErfSize
FROM dbo.__Mandate INNER JOIN
dbo.__MandateType ON dbo.__Mandate.MandateTypeId = dbo.__MandateType.Id LEFT OUTER JOIN
dbo.__MandateListing ON dbo.__Mandate.Id = dbo.__MandateListing.MandateId LEFT OUTER JOIN
dbo.__MandateFeature AS BedroomFeature ON dbo.__Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.__Feature AS __Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.__Feature ON BedroomFeature.FeatureId = dbo.__Feature.Id
GROUP BY dbo.__Mandate.Id, dbo.__MandateType.MandateType, dbo.__Mandate.ErfSize
..but this of course only displays the number of bedrooms. As soon as I duplicate this part:
LEFT OUTER JOIN
dbo.__MandateFeature AS BedroomFeature ON dbo.__Mandate.Id = BedroomFeature.MandateId AND BedroomFeature.FeatureId =
(SELECT Id
FROM dbo.__Feature AS __Feature_1
WHERE (Feature = 'Bedroom')) LEFT OUTER JOIN
dbo.__Feature ON BedroomFeature.FeatureId = dbo.__Feature.Id
and modify it to this:
LEFT OUTER JOIN
dbo.__MandateFeature AS BathroomFeature ON dbo.__Mandate.Id = BathroomFeature.MandateId AND BathroomFeature.FeatureId =
(SELECT Id
FROM dbo.__Feature AS __Feature_2
WHERE (Feature = 'Bathroom')) LEFT OUTER JOIN
dbo.__Feature AS __Feature_2 ON BathroomFeature.FeatureId = dbo.__Feature.Id
..and add COUNT(BathroomFeature.FeatureId) AS Bathrooms to the SELECT, it displays the bedrooms and bathrooms, but the values are duplicated across these 2 columns (and it's not the correct values either)
modified on Wednesday, April 20, 2011 5:49 AM
|
|
|
|
|
Move your sub query from the join to the select and add the mandateID to the where clause. This can be repeated for different feature types.
Caveat this type of sub select can be very slow and expensive.
Another way would be to left join out to the feature table multiple times (1 for each feature type) using a case statement on the if field (case featureid when is null then 0 else 1 end), group by your mandate fields and sum each feature field.
Another way is to use the same multiple joins and then pivot the rows so each feature is in it's own column.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
