May 19, 2010 at 7:27 am
I am sorry, i am posting more questions, i am JAVA developer, not much about SQL. So please help me
Please some one help me to convert this JAVA code to a SQL LOGIC 🙂
Date lockoutFromDate = new GregorianCalendar(2010, (5-1)*3, 1).getTime()
Date lockoutToDate = new GregorianCalendar(2010, (5-1)*3, 4).getTime()
Date currentDate = new Date();
if(lockoutFromDate != null && lockoutToDate != null) {
if(currentDate.compareTo(lockoutFromDate) >= 0 &¤tDate.compareTo(lockoutToDate) <= 0) {
lockout = true;
}
}
Thanks in advance
May 19, 2010 at 7:39 am
Since this is a SQL forum. . . how about you post what the code is meant to do rather than just posting code. That way someone might decide to help.
May 19, 2010 at 7:52 am
I am sorry, i dint think in that way
Here goes my code explanation
These two lines creates a normal date from the specification of
new GregorianCalendar(year, month, day of month). so in SQL if ic ould such simlilar func wher i can specify month date year, and get the date, will be appreciated.
Date lockoutFromDate = new GregorianCalendar(2010, (5-1)*3, 1).getTime()
Date lockoutToDate = new GregorianCalendar(2010, (5-1)*3, 4).getTime()
Date currentDate = new Date();
first if loop is null check, i think it is not needed
if(lockoutFromDate != null && lockoutToDate != null) {
if the current date is in between the lockoutFromDate and lockoutToDate then set a boolean varaible true
if(currentDate.compareTo(lockoutFromDate) >= 0 && currentDate.compareTo(lockoutToDate) <= 0) {
lockout = true;
}
}
May 19, 2010 at 8:03 am
Assuming I've understood you. . . this should be OK.
DECLARE @theDate VARCHAR(50)
DECLARE @startDate VARCHAR(50)
DECLARE @endDate VARCHAR(50)
SET @theDate='1 jan 2010'
SET @startDate='31 dec 2007'
SET @endDate='1 jan 2009'
SELECT CASE
WHEN CAST(@theDate AS DATETIME) BETWEEN @startDate AND @endDate THEN 'True'
ELSE 'False'
END
Let us know if that's what you're after!
May 19, 2010 at 8:07 am
Thanks, i hope this will work, before give a try can u help to add a small thing, is that possible to generate the start date and end date dynamically by giving date and month and year.. as i did in JAVA
Thanks
May 19, 2010 at 8:10 am
SET @theDate = GETDATE()
May 19, 2010 at 8:15 am
Thanks jvanderberg, you have answered part of my question, i need to set my start date and end date evn, by passing month, year, and date. Can you help me in that
May 19, 2010 at 8:22 am
DECLARE @theDate VARCHAR(50)
DECLARE @startDate VARCHAR(50)
DECLARE @endDate VARCHAR(50)
DECLARE @month VARCHAR(15)
DECLARE @day INTEGER
DECLARE @year INTEGER
--Get the current date
SET @theDate=getDate()
--Set the month, day and year for the start
SET @month='January'
SET @day='1'
SET @year='2009'
--Set the startdate
SET @startDate=CAST(@day AS VARCHAR) + ' ' + @month + ' ' + CAST(@year AS VARCHAR)
--Set the month, day and year for the end
SET @month='May'
SET @day='20'
SET @year='2010'
--Set the enddate
SET @endDate=CAST(@day AS VARCHAR) + ' ' + @month + ' ' + CAST(@year AS VARCHAR)
--Is the current date between?
SELECT CASE
WHEN CAST(@theDate AS DATETIME) BETWEEN @startDate AND @endDate THEN 'True'
ELSE 'False'
END
Is that what you meant?
May 19, 2010 at 8:22 am
pattamuthu (5/19/2010)
Thanks, i hope this will work, before give a try can u help to add a small thing, is that possible to generate the start date and end date dynamically by giving date and month and year.. as i did in JAVAThanks
To specify by month/day/year, you could probably use convert
DECLARE @date DATETIME
DECLARE @month INT, @day INT, @year INT
SELECT @month = 12, @day = 31, @year = 1990
SELECT @date = CONVERT(DATETIME,CAST(@month AS VARCHAR) + '/' + CAST(@day AS VARCHAR) + '/' + CAST(@year AS VARCHAR), 101)
May 19, 2010 at 8:31 am
Date lockoutFromDate = new GregorianCalendar(2010, (5-1)*3, 1).getTime()
Where are you getting these numbers from? Where are you going to write the SQL -- will it be an SP or a parameterized query called from the code?
If it's an SP you can pass those values (that define year, day & month) as parameters; carry out the calculations as it's being done in the code and then use the techniques mentioned in the above posts to create your date.
edit: spelling mistakes
May 19, 2010 at 8:38 am
Thank you very much..
In my Stored procedure i have written like this
DECLARE @isLockOutPeriod BIT
SELECT CASE
WHEN CAST(@currentDate AS DATETIME) BETWEEN @lockOutStartDate AND @lockOutEndDate
THEN SET @isLockOutPeriod = 1
ELSE SET @isLockOutPeriod = 2
END
it gives me
Msg 102, Level 15, State 1, Procedure O2_SERVICES, Line 96
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure O2_SERVICES, Line 101
Incorrect syntax near 'END'.
What went wrong in this?
Please help
May 19, 2010 at 8:45 am
Ah! Now I understand what you wanted! :hehe:
DECLARE @isLockOutPeriod BIT
SET @isLockOutPeriod = CASE
WHEN CAST(@currentDate AS DATETIME) BETWEEN @lockOutStartDate AND @lockOutEndDate THEN 1
ELSE 2
END
May 19, 2010 at 8:57 am
You can set a BIT field to 2, but it will resolve to 1.
DECLARE @bit BIT
SET @bit = 2
SELECT @bit
Returns 1.
Instead:
SET @isLockOutPeriod = CASE
WHEN CAST(@currentDate AS DATETIME) BETWEEN @lockOutStartDate AND @lockOutEndDate THEN 1
ELSE 0
END
Set it to 0 for FALSE or 1 for TRUE.
May 19, 2010 at 9:08 am
Thank you very much, will some one help me in this..
http://www.sqlservercentral.com/Forums/Topic923666-23-1.aspx
the same problem, but a small change required, please help me in this
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply