Manipulating dates in SQL

I need to generate a date range, based on the current date (or an input date). I can get the correct dates using VB, but I haven't worked out the TSQL Syntax for them yet. Can anyone tell me the TSQL syntax for manipulating dates in the following way...?

Start Date... This is the first day of the month, one year ago... in VB I worked it out as...

dateadd("yyyy",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

End Date... This is the last day of the previous month... in VB I worked this one out as...

dateadd("d",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))

eg. for today 18/01/2007 I would get a Start date of 01/01/2006 and an End date of 31/12/2006

Any help would be appreciated.

I managed to work out a solution... I get my Start date by using the following...

(select dateadd(yyyy,-1,(select stuff(stuff((convert(varchar,
convert(varchar,datepart(year,getdate()))+
convert(varchar,datepart(Month,getdate()))+
convert(varchar,(convert(int,datepart(day,getdate())))- (convert(int,datepart(day,getdate()))-1))))
,5 ,0, '-'),7,0, '-'))))

|||

Hey Jon,

This should do what you want it to.

select dateadd(mm, datediff(mm, 0, dateadd(yy, -1, getdate())), 0) as StartDate,
dateadd(dd, -1, dateadd(mm, datediff(mm, 0, getdate()), 0)) as EndDate

Hope this helps.

Jarret

|||

That's great... thanks Jarret.

A much better solution than mine... especially since I noticed mine only works if the month is a single digit.

Managing DATE ?

Hi,

I have a page that shows some problem indexes (cards ? I don't know the word :/ ) and I want to show only those that are from the current day.

I'm using SQL server 2000. I have a date field inside my table (datetime type). So I tried to put another condition in my WHERE clause. This is:

WHERE something = something else AND mydate = DATEPART('dd', getdate())

or

WHERE something = something else AND mydate = DAY(getdate())

Both don't work..

I wonder if I can really use this in a WHERE clause...of if I'm using them correctly.try:

WHERE mydate BETWEEN CONVERT(datetime,CONVERT(nvarchar(20),GetDate(),101)) AND
CONVERT(datetime,CONVERT(nvarchar(20),DATEADD(day,1,GetDate()),101))

This will get any date/time between midnight last night and midnight tonight.|||That's it, thanks a lot.

But if it is not too much, can I ask why do I have to convert the whole thing (I thought DATE functions could handle datetime type ?) and what does 101 stand for ?|||i'll let douglas answer your question, but I'd be more comfortable with..

" ... WHERE something = something else AND mydate BETWEEN @.StartDate AND @.EndDate"

commandobject.Parameters.Add("@.StartDate", SqlDbType.DateTime);
commandobject.Parameters.Add("@.EndDate", SqlDbType.DateTime);

commandobject.Parameters["@.StartDate"].Value = thestartdatevar;
commandobject.Parameters["@.EndDate"].Value = theenddatevar;|||I presumed that this particular bit of coding was all on the SQL Server box itself. The only reason for using the two dates is so that any entry <b>on a particular day</a> is selected. In the example, he wanted records for today. Thus, no parameters would need to be sent (unless this needs to be generalized).

101 means that the date is formatted mm/dd/yyyy. In thinking about it, 112 (yyyymmdd) would have been better. A DateTime type has both a date AND a time. You are looking to get just a particular date (today in your example).|||Ok.

But is there a way to write a DateTime (like 2004-01-01) and use it with the code you gave me. Because if I want to do an archive of all entries, I have to use specific date (something like clickable month).

I think I should use KraGie code, but how do I initialise DateTime variable ?

For instance, if I want thestartdatevar to be June 1st 2004 (for the moment, because I'll eventually pass the value through a DropDownList or something).

thestartdatevar = ?

PS: Is this C# (the ; ?) ? Because I don't know C#.

Managing Big Database

H
I have a Database over 50G. It increases 1G per day
Today I detach the current database and move the data&log file to E drive. Then I use DTS transfering those data to a new database on D drive. The database becomes 18G!!!! I check the tables, everything is there.
How did this happen? I thought there must be something about fragments. But I am not sure. Can anyone tell me
And is there any other way to defrag the database without taking off-line
thank you very muchDBCC ShrinkDatabase?
Most of the suggestions in http://www.aspfaq.com/2446 apply to any database.
Also see http://www.aspfaq.com/2471
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...
> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.
Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.
> How did this happen? I thought there must be something about fragments.
But I am not sure. Can anyone tell me?
> And is there any other way to defrag the database without taking off-line?
>
> thank you very much|||I have found the cause! It's because that I didn't build the index.|||I shrink database every day. The log file is quite small.|||and make sure you take backups of the database in order to be able to shrink
the transaction log in case of a full recovery model.
Check books online on dbcc shrinkdatabase, dbcc shrinkfile, and shrinking
the transaction log
--
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...
> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.
Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.
> How did this happen? I thought there must be something about fragments.
But I am not sure. Can anyone tell me?
> And is there any other way to defrag the database without taking off-line?
>
> thank you very much

Managing Big Database

Hi
I have a Database over 50G. It increases 1G per day!
Today I detach the current database and move the data&log file to E drive. T
hen I use DTS transfering those data to a new database on D drive. The data
base becomes 18G!!!! I check the tables, everything is there.
How did this happen? I thought there must be something about fragments. But
I am not sure. Can anyone tell me?
And is there any other way to defrag the database without taking off-line?
thank you very muchDBCC ShrinkDatabase?
Most of the suggestions in http://www.aspfaq.com/2446 apply to any database.
Also see http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...

quote:

---

> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.

---

Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.

quote:

---

> How did this happen? I thought there must be something about fragments.

---

But I am not sure. Can anyone tell me?

quote:

---

> And is there any other way to defrag the database without taking off-line?
>
> thank you very much

---

|||I have found the cause! It's because that I didn't build the index.|||I shrink database every day. The log file is quite small.|||and make sure you take backups of the database in order to be able to shrink
the transaction log in case of a full recovery model.
Check books online on dbcc shrinkdatabase, dbcc shrinkfile, and shrinking
the transaction log
Regards,
Dandy Weyn
MCSE, MCSA, MCDBA, MCT
www.dandyman.net
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:795D47D7-4A98-4907-8B2D-452A172EC1CD@.microsoft.com...

quote:

---

> Hi
> I have a Database over 50G. It increases 1G per day!
> Today I detach the current database and move the data&log file to E drive.

---

Then I use DTS transfering those data to a new database on D drive. The
database becomes 18G!!!! I check the tables, everything is there.

quote:

---

> How did this happen? I thought there must be something about fragments.

---

But I am not sure. Can anyone tell me?

quote:

---

> And is there any other way to defrag the database without taking off-line?
>
> thank you very much

---