Reset date to first of month

I receive an existing date variable, which I copy to my @.today variable. I
need to reset the day of the month to be the first of the month, leaving all
other aspects of @.today alone.
Any suggestions?
-- simulate the date I get, which I have no control over
declare @.date datetime
set @.date = '02/08/2008'
--assign to my date variable, which I do control
declare @.today datetime
SET @.today = @.date
--the following doesn't work, but it shows what I want to do
SET MONTH(@.today) = 1
SELECT @.today
Thanks
--
RandySET @.today = DATEADD(month,DATEDIFF(month,0,@.date),0)
Steve Kass
Drew University
randy1200 wrote:

>I receive an existing date variable, which I copy to my @.today variable. I
>need to reset the day of the month to be the first of the month, leaving al
l
>other aspects of @.today alone.
>Any suggestions?
>-- simulate the date I get, which I have no control over
>declare @.date datetime
>set @.date = '02/08/2008'
>--assign to my date variable, which I do control
>declare @.today datetime
>SET @.today = @.date
>--the following doesn't work, but it shows what I want to do
>SET MONTH(@.today) = 1
>SELECT @.today
>Thanks
>|||You can try using:
-- simulate the date I get, which I have no control over
declare @.date datetime
set @.date = '02/08/2008'
--assign to my date variable, which I do control
declare @.today datetime
SET @.today = @.date
--If I understood correctly
set @.today = @.today - (DAY(@.today)-1)
SELECT @.today
Let me know if it helps..
"randy1200" wrote:

> I receive an existing date variable, which I copy to my @.today variable. I
> need to reset the day of the month to be the first of the month, leaving a
ll
> other aspects of @.today alone.
> Any suggestions?
> -- simulate the date I get, which I have no control over
> declare @.date datetime
> set @.date = '02/08/2008'
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --the following doesn't work, but it shows what I want to do
> SET MONTH(@.today) = 1
> SELECT @.today
> Thanks
> --
> Randy|||That did it. Many thanks.
--
Randy
"Edgardo Valdez, MCSD, MCDBA" wrote:
> You can try using:
> -- simulate the date I get, which I have no control over
> declare @.date datetime
> set @.date = '02/08/2008'
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --If I understood correctly
> set @.today = @.today - (DAY(@.today)-1)
> SELECT @.today
> Let me know if it helps..
> "randy1200" wrote:
>|||That did it. Many thanks.
--
Randy
"Steve Kass" wrote:

> SET @.today = DATEADD(month,DATEDIFF(month,0,@.date),0)
> Steve Kass
> Drew University
> randy1200 wrote:
>
>|||This one will work for the first date as well:
set @.today = @.today - (case when DAY(@.today) = 1 then 0 else DAY(@.today)-1
end)
"randy1200" wrote:
> That did it. Many thanks.
> --
> Randy
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Very . Many thanks again!
--
Randy
"Edgardo Valdez, MCSD, MCDBA" wrote:
> This one will work for the first date as well:
> set @.today = @.today - (case when DAY(@.today) = 1 then 0 else DAY(@.today)-1
> end)
> "randy1200" wrote:
>|||You are very welcome!
"randy1200" wrote:
> Very . Many thanks again!
> --
> Randy
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Hi Randy,
Try:
select DATEADD(mm, DATEDIFF(mm,0,@.today), 0)
"DATEDIFF(mm,0,getdate())" calculates the number of months between the curre
nt
date and the date "1900-01-01 00:00:00.000".
Remember date and time variables are stored as the number of milliseconds
since "1900-01-01 00:00:00.000"; this is why you can specify the first datet
ime
expression of the DATEDIFF function as "0."
Now the last function call, DATEADD, adds the number of months between the
current date and '1900-01-01".
By adding the number of months between our pre-determined date '1900-01-01'
and the current date, you are able to arrive at the first day of the current
month.
In addition, the time portion of the calculated date will be "00:00:00.000."

> I receive an existing date variable, which I copy to my @.today
> variable. I
> need to reset the day of the month to be the first of the month,
> leaving all
> other aspects of @.today alone.
> Any suggestions?
> --assign to my date variable, which I do control
> declare @.today datetime
> SET @.today = @.date
> --the following doesn't work, but it shows what I want to do
> SET MONTH(@.today) = 1
> SELECT @.today
> Thanks
>sql

requesting querry to insert date and time

Hi,
Can anybody telll me how to give date and time to be printed in
sqlserver programmin.For example if u want to add data to a table using
insert command how can we enter date and time in that.
please reply me soon.
thanks for helping.Did you have a look on the getdate() function ?
INSERt INTO SomeTable
( colList ... )
VALUES
(
...
GETDATE(),
...
)
HTH, Jens Suessmeyer.|||Do you mean something like GETDATE() . This returns the system time and date
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"gija" <girijak@.hotmail.com> wrote in message
news:1140035764.737565.125890@.g44g2000cwa.googlegroups.com...
> Hi,
> Can anybody telll me how to give date and time to be printed in
> sqlserver programmin.For example if u want to add data to a table using
> insert command how can we enter date and time in that.
> please reply me soon.
> thanks for helping.
>|||INSERT table(column) SELECT '2006-02-15T05:43:21';
INSERT table(column) VALUES('2006-02-15T05:43:21');
"gija" <girijak@.hotmail.com> wrote in message
news:1140035764.737565.125890@.g44g2000cwa.googlegroups.com...
> Hi,
> Can anybody telll me how to give date and time to be printed in
> sqlserver programmin.For example if u want to add data to a table using
> insert command how can we enter date and time in that.
> please reply me soon.
> thanks for helping.
>

request Stored procedure filtered by Today

I am using SQL2005.There si a field called"EXPDATE". I need a query that shows the table info, if the date that is exist on "EXPDATE" is greater than today. In summary How to write a code that if EXPDATE> "today (I do not know what to put instead of today)" then show the contents of date

In SqlServer, today's function is GetDate()|||

Yes in T-SQL we use GETDATE() to get today'date, but remember that the GETDATE() function will return a DATETIME value which also contains time. So if you just want to compare date, you may need something like this (suppose the EXPDATE column is also DATETIME data type):

select * fromyourTable
WHERE DATEDIFF(d,GETDATE(),EXPDATE)>0

Requery Parameter Fields

I have a report that runs on a number of parameter date fields (coverted to string). At present, the report automatically defaults to MAX value of a date. The syntax for that particular element/dataset is

SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
FROM tbl_src_date

What I am looking to acheive is that if I was to modify the parameter to allow the user to specifiy a date from that particular field (rather than default to MAX), how could I get the subsequent parameter boxes to requery automatically. The code I use for the other 5 parameter fields is;

SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-1, MAX(src_date))) AS src_date_pre
FROM tbl_src_date

SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-2, MAX(src_date))) AS src_date_pre2
FROM tbl_src_date

SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-3, MAX(src_date))) AS src_date_pre3
FROM tbl_src_date

SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-4, MAX(src_date))) AS src_date_pre4
FROM tbl_src_date

SELECT CONVERT(char(11), MAX(src_date)) AS src_date_cur
, CONVERT(char(11), dateadd(mm,-5, MAX(src_date))) AS src_date_pre5
FROM tbl_src_date

If it makes a difference I am using SS-RS 2005.

Regards

Make them source from a dataset which has a dependency on the Parameter. This will create a hierarchy, whereas the first parameter value (e.g. a datetime picker has to be selected first)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

repost on subtracting date range data between tables

Hi everyone. I posted this previously but made an error in my original
posting. So i am posting again with corrections in place, in hopes someone
might shed light on a possible solution.
What i have is two tables containing date range date. I need to find the
difference between this date. So for example if i have:
table a:
[start] [finish]
1 10
18 19
23 26
28 31
table b:
[start] [finish]
4 5
18 18
25 28
Then the result of table a - table b:
[start] [finish]
1 3
6 10
19 19
23 24
29 31
in effect, if a date range in table_b intersects a date range in table_a,
then that data in the intersection is removed from the date range in table_a
for example, the following are example cases where we subtract from a range
in table_a where there are ranges in table_b which intersect with that range
in table_a:
[From Table A ] - [ From Table B]:
1. {23....37} - {25...29} = {23...24}
2. {23....37} - {26...32} = {23...25}, {33...37}
3. {23....37} - [ {25...27} , {31...33} ] = {23...24}, {28...30},
{34...37}
I am considering solving this problem by using: A - (A AND B). Here A AND B
is a pure subset of A. Maybe this would make the problem easier to solve.
Any help on this would be really appreciated!
Many thanks.
peter-- There are probably lots of ways of doing this, the code
-- below uses recursive CTEs (you'll need SQL Server 2005)
CREATE TABLE TabA(start INT, finish INT)
INSERT INTO TabA(start,finish)
SELECT 1, 10 UNION ALL
SELECT 18, 19 UNION ALL
SELECT 23, 26 UNION ALL
SELECT 28, 31;
CREATE TABLE TabB(start INT, finish INT)
INSERT INTO TabB(start, finish)
SELECT 4, 5 UNION ALL
SELECT 18, 18 UNION ALL
SELECT 25, 28;
WITH CTE_Recur(startgrp,finishgrp,level,start
,finish) AS
(
SELECT a.start,a.finish,1,a.start,a.finish
FROM TabA a
WHERE EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
AND NOT EXISTS (SELECT * FROM TabB b WHERE a.start>=b.start AND
a.finish<=b.finish)
UNION ALL
SELECT a.startgrp,a.finishgrp,level+1,a.start,b.start-1
FROM CTE_Recur a
INNER JOIN TabB b ON a.start<b.start AND a.finish>=b.start
UNION ALL
SELECT a.startgrp,a.finishgrp,level+1,b.finish+1,a.finish
FROM CTE_Recur a
INNER JOIN TabB b ON a.finish>b.finish AND a.start<=b.finish
),
CTE_Leaves(startgrp,finishgrp,level) AS
(
SELECT startgrp,finishgrp,max(level)
FROM CTE_Recur
GROUP BY startgrp,finishgrp
)
SELECT r.start,r.finish
FROM CTE_Recur r
INNER JOIN CTE_Leaves l ON l.startgrp=r.startgrp AND
l.finishgrp=r.finishgrp AND l.level=r.level
UNION
SELECT a.start,a.finish
FROM TabA a
WHERE NOT EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
ORDER BY 1,2|||hi, thanks for that.but would you know how to write this is sql 2000?
much appreciated
peter
<markc600@.hotmail.com> wrote in message
news:1137272625.701449.190200@.g43g2000cwa.googlegroups.com...
> -- There are probably lots of ways of doing this, the code
> -- below uses recursive CTEs (you'll need SQL Server 2005)
>
> CREATE TABLE TabA(start INT, finish INT)
> INSERT INTO TabA(start,finish)
> SELECT 1, 10 UNION ALL
> SELECT 18, 19 UNION ALL
> SELECT 23, 26 UNION ALL
> SELECT 28, 31;
> CREATE TABLE TabB(start INT, finish INT)
> INSERT INTO TabB(start, finish)
> SELECT 4, 5 UNION ALL
> SELECT 18, 18 UNION ALL
> SELECT 25, 28;
> WITH CTE_Recur(startgrp,finishgrp,level,start
,finish) AS
> (
> SELECT a.start,a.finish,1,a.start,a.finish
> FROM TabA a
> WHERE EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
> a.finish>=a.start)
> AND NOT EXISTS (SELECT * FROM TabB b WHERE a.start>=b.start AND
> a.finish<=b.finish)
> UNION ALL
> SELECT a.startgrp,a.finishgrp,level+1,a.start,b.start-1
> FROM CTE_Recur a
> INNER JOIN TabB b ON a.start<b.start AND a.finish>=b.start
> UNION ALL
> SELECT a.startgrp,a.finishgrp,level+1,b.finish+1,a.finish
> FROM CTE_Recur a
> INNER JOIN TabB b ON a.finish>b.finish AND a.start<=b.finish
> ),
> CTE_Leaves(startgrp,finishgrp,level) AS
> (
> SELECT startgrp,finishgrp,max(level)
> FROM CTE_Recur
> GROUP BY startgrp,finishgrp
> )
> SELECT r.start,r.finish
> FROM CTE_Recur r
> INNER JOIN CTE_Leaves l ON l.startgrp=r.startgrp AND
> l.finishgrp=r.finishgrp AND l.level=r.level
> UNION
> SELECT a.start,a.finish
> FROM TabA a
> WHERE NOT EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
> a.finish>=a.start)
> ORDER BY 1,2
>|||-- This isn't quite equivalent and has some restrictions
-- such as table B must not have any overlapping ranges,
-- but will work on SQL Server 2000
CREATE TABLE TabA(start INT, finish INT)
INSERT INTO TabA(start,finish)
SELECT 1, 10 UNION ALL
SELECT 18, 19 UNION ALL
SELECT 23, 26 UNION ALL
SELECT 28, 31;
CREATE TABLE TabB(start INT, finish INT)
INSERT INTO TabB(start, finish)
SELECT 4, 5 UNION ALL
SELECT 18, 18 UNION ALL
SELECT 25, 28;
SELECT COALESCE((SELECT MAX(b2.finish)+1 FROM TabB b2 WHERE b2.finish <
b.start and b2.finish > a.start),a.start) as start,
b.start-1 as finish
FROM TabA a
INNER JOIN TabB b ON a.start<b.start AND a.finish>=b.start
WHERE EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
AND NOT EXISTS (SELECT * FROM TabB b WHERE a.start>=b.start AND
a.finish<=b.finish)
UNION
SELECT b.finish+1,
COALESCE((SELECT MIN(b2.start)-1 FROM TabB b2 WHERE b2.start >
b.finish and b2.start < a.finish),a.finish)
FROM TabA a
INNER JOIN TabB b ON a.finish>b.finish AND a.start<=b.finish
WHERE EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
AND NOT EXISTS (SELECT * FROM TabB b WHERE a.start>=b.start AND
a.finish<=b.finish)
UNION
SELECT a.start,a.finish
FROM TabA a
WHERE NOT EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
ORDER BY 1,2|||On Sun, 15 Jan 2006 04:45:27 +1000, peter walker wrote:

>Hi everyone. I posted this previously but made an error in my original
>posting. So i am posting again with corrections in place, in hopes someone
>might shed light on a possible solution.
(snip)
Hi Peter,
I just posted a reply to your first message about this problem.
Hugo Kornelis, SQL Server MVP|||yet another approach would be to use a calendar table:
select [date] date_in_range from calendar c
where exists(select 1 from a
where c.[date] between a.[start] and a.[finish])
and not exists(select 1 from b
where c.[date] between b.[start] and b.[finish])
The query will return a set of dates. If you need intervals, that's
also quite easy to accomplish