db backup simple vs. full recovery mode

When we do a full database backup manually, we are seeing the trn file reflect the current date/time, but we are not seeing the mdf reflect the new date/time. And we are not seeing the transaction log file decrease in size. the recovery mode is set to full, do we need to change to simple to see both the mdf being backup'ed?

When you do a backup, markers are written to the Transaction Log file, however, the backup process does not change anything about the datafiles -therefore the 'trn' file gets a new datetime and the data file does not.

The Transaction Log file does not shrink UNLESS specifically so instructed. See Books Online for DBCC 'Shrinkfile'.

|||

Hi,

You can schedule half/hourly t-log backup to keep it in shape, how ever if its growing unpexctingly refer below thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1221599&SiteID=1

Hemantgiri S. Goswami

|||

What we are seeing are current timestamps on the trn file, current timestamps on the ldf, but about a six month old modified date on the mdf. I would assume that the trn file would have the most recent transactions, the ldf the intermediate, and then the mdf.

With the truncate command on the trn file, do the transactions immediately hit the mdf file or the ldf (I would think the ldf)? however when does the mdf get updated by the ldf file?

Am I completely lost--I thought that the ldf (a locked mdf file, correct?) would eventually post the edits/updates to the mdf.

|||

The ldf is the transaction log file. Data changes are moved to the mdf (data file) on a regular basis -usually within seconds.

The OS stamps the file date. SQL Server has a data file (mdf) open with a, perhaps, large, amount of empty space. The OS does not know what is happening inside the mdf file unless there are specific interactions between SQL Server and the OS regarding the file.

It seems like you are confused because the mdf file date is not changing. It most likely will not change unless one of the following actions occur: Filegrowth, Fileshrink, Detach/Attach.

DaysOfWeek CheckBoxList

I have a checkboxlist of days of the week that I wish to be checked if
they are in the current schedule.
Is there a neater way of accomplishing this than the code I have at
present?
Dim recurrence As New WeeklyRecurrence
recurrence = CType(schedule.Definition.Item, WeeklyRecurrence)
If recurrence.DaysOfWeek.Sunday Then
cblScheduleWeeklyDays.Items.FindByValue(0).Selected = True
End If
If recurrence.DaysOfWeek.Monday Then
cblScheduleWeeklyDays.Items.FindByValue(1).Selected = True
End If
If recurrence.DaysOfWeek.Tuesday Then
cblScheduleWeeklyDays.Items.FindByValue(2).Selected = True
End If
If recurrence.DaysOfWeek.Wednesday Then
cblScheduleWeeklyDays.Items.FindByValue(3).Selected = True
End If
If recurrence.DaysOfWeek.Thursday Then
cblScheduleWeeklyDays.Items.FindByValue(4).Selected = True
End If
If recurrence.DaysOfWeek.Friday Then
cblScheduleWeeklyDays.Items.FindByValue(5).Selected = True
End If
If recurrence.DaysOfWeek.Saturday Then
cblScheduleWeeklyDays.Items.FindByValue(6).Selected = True
End IfObviously this would be better
cblScheduleWeeklyDays.Items.FindByValue(0).Selected =recurrence.DaysOfWeek.Sunday
cblScheduleWeeklyDays.Items.FindByValue(1).Selected =recurrence.DaysOfWeek.Monday
cblScheduleWeeklyDays.Items.FindByValue(2).Selected =recurrence.DaysOfWeek.Tuesday
cblScheduleWeeklyDays.Items.FindByValue(3).Selected =recurrence.DaysOfWeek.Wednesday
cblScheduleWeeklyDays.Items.FindByValue(4).Selected =recurrence.DaysOfWeek.Thursday
cblScheduleWeeklyDays.Items.FindByValue(5).Selected =recurrence.DaysOfWeek.Friday
cblScheduleWeeklyDays.Items.FindByValue(6).Selected =recurrence.DaysOfWeek.Saturday
Not very elegant I know, but less code.
I would prefer a for each ... construct

Day of the week

How to find the saturday in the current week using sql server 2000

SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),5)

This assumes the DATEFIRST is set to 7 on the sql server. 7 meaning the first day of the week is Sunday.

|||

How to get the 11:59 pm of this saturday. I get the output of running the query as
2007-05-26 00:00:00.000
but I want some thing like below:
2007-05-26 11:59:00.000

|||

There are many methods to do this, but I might use something like this:

SELECTDATEADD( minute , -1 ,DATEADD( wk ,DATEDIFF( wk, 0,getdate() ) , 6))

|||

You are better off getting Sunday and checking to see if the value is less than that, rather than less than or equal to 11:59pm of Saturday. Unless of course you can never ever have 11:59:02pm on Saturday.

|||

Good point Motley. I do try to stress the date predicate style that is not like

WHERE dateColBETWEEN @.dt1AND @.dt2

which is an all-inclusive between hard dates but rather an inclusive lower, exclusive upper style

WHERE dateCol >= @.dt1AND dateCol < @.dt2

Which allows for variations in date precision. Not exactly sure where the original poster was going with that, but nonetheless...

DateTime without the time

Hi,

Im moving data from a OLE DB Source to a Flat File Destination.

I have a DateTime field in my database.

My current query returns:
2007-05-21 00:00:00

How can I make it return:
2007-05-21

Thank you!!

Use a derived column to cast the field to DT_DBDATE...

(DT_DBDATE)[YourDateTimeField]|||

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

|||

MrHat wrote:

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

Yes, you need to define the data type of that column to DT_DBDATE in the flat file connection manager.|||My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?|||

JStutz wrote:

My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?

Displaying just the time is a simple transact-sql statement using the CONVERT function.

|||

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

|||

SQL-PRO wrote:

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

Still if that's in your source query, you can't store it that way -- not in SQL Server anyway. (Unless you're storing it in a varchar field.)

DateTime without the time

Hi,

Im moving data from a OLE DB Source to a Flat File Destination.

I have a DateTime field in my database.

My current query returns:
2007-05-21 00:00:00

How can I make it return:
2007-05-21

Thank you!!

Use a derived column to cast the field to DT_DBDATE...

(DT_DBDATE)[YourDateTimeField]|||

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

|||

MrHat wrote:

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

Yes, you need to define the data type of that column to DT_DBDATE in the flat file connection manager.|||My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?|||

JStutz wrote:

My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?

Displaying just the time is a simple transact-sql statement using the CONVERT function.

|||

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

|||

SQL-PRO wrote:

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

Still if that's in your source query, you can't store it that way -- not in SQL Server anyway. (Unless you're storing it in a varchar field.)

DateTime question

I'm trying to update a datetime field in my database with the current
date and time. The following command inserts the date as 1/13/06 when
most systems recognize 38728 as 1/11/06. Why does sql server 2005
increase this value by two days. The code that uses this database is
written in C# and makes heavy use of DateTime especially DateTime.Now,
which thinks 38728 is 1/11/06 so I'm not looking for a different way to
add this record, I want sql server to understand that 38728 is 1/11/06
and not 1/13/06.
Please help!
INSERT INTO [dbo].[Test]
([EID]
,[modifiedBy]
,[modifiedOn])
VALUES
(9999,
'TestHarness',
38728)What result does the following give you :-
select @.@.datefirst
Try SET @.@.DateFirst (two less than the result from above) before running
your insert
--
HTH. Ryan
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||You don't use the product correctly. You express datetimes in SQL Server as a string, not as a
number. Unfortunately, SQL Server accepts a number (implicit datatype conversion) and thereby
exposes the internals of the product. And that happens to be different from some other systems, as
you have noticed. You cannot change the behavior in this regard. Check out:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>|||Why not just take the c# datetime and convert to SqlDateTime and store that?
--
William Stacey [MVP]
"BetaD" <dhorth@.horth.com> wrote in message
news:1136994628.541049.108210@.g44g2000cwa.googlegroups.com...
> I'm trying to update a datetime field in my database with the current
> date and time. The following command inserts the date as 1/13/06 when
> most systems recognize 38728 as 1/11/06. Why does sql server 2005
> increase this value by two days. The code that uses this database is
> written in C# and makes heavy use of DateTime especially DateTime.Now,
> which thinks 38728 is 1/11/06 so I'm not looking for a different way to
> add this record, I want sql server to understand that 38728 is 1/11/06
> and not 1/13/06.
> Please help!
> INSERT INTO [dbo].[Test]
> ([EID]
> ,[modifiedBy]
> ,[modifiedOn])
> VALUES
> (9999,
> 'TestHarness',
> 38728)
>sql

DateTime Parameter in URL

Hi Group,

How can I amend the following to pass the current date as the hidden parameter?
I tried Now() and Date() but this does not work

<form id="frmRender" action="http://Server1/ReportServer?/Reports/Charts/Pie" method="post"
target="_self">
<input type="hidden" name="rs:Command" value="Render&CreationDate">
<input type="hidden" name="rc:Toolbar" value="false">
<input type = "hidden" name=CreationDate value= '01/06/2004'>
<input type="submit" value="view report">
</form>

cheers

Try using javascript on the body onload event.

<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">
function setCreationDate()
{
var now = new Date();
document.Form1.CreationDate.value = now;
}
</SCRIPT>
</HEAD>
<body onload="setCreationDate()">
<form id="frmRender" action="http://Server1/ReportServer?/Reports/Charts/Pie" method="post"
target="_self">
<input type="hidden" name="rs:Command" value="Render&CreationDate">
<input type="hidden" name="rc:Toolbar" value="false">
<input type ="hidden" name=CreationDate value=''>
<input type="submit" value="view report">
</form>
</body>
</HTML>

DateTime help

This simply shouldn't take all morning to figure out but for some reason it has. I simply want to insert the current date and time into a datetime field.

No matter what I try I either get errors (Syntax error converting datetime from character string.) or I get the wrong date (4/11/1900, 1/1/1900).

Here's my current SQL which gives the syntax error.

CREATE PROCEDURE [dbo].[QuoteApprovalWeb_Approve]
@.table nvarchar(50),
@.approvedby nvarchar(100),
@.quote nvarchar(50),
@.dt datetime
AS
Declare @.SQL nVarchar(4000)
Select @.SQL = 'Update [' + @.table + '] set quoteapproval = ' + "'" + @.approvedby + "', "
Select @.SQL = @.SQL + 'quoteapprovaldate = ' + @.dt + ' where quoteno ='
Select @.SQL = @.SQL + "'" + @.quote + "'"
exec (@.sql)
GO

I tried replacing @.dt with getdate() but that would always give me errors also.replace the line

Select @.SQL = @.SQL + 'quoteapprovaldate = ' + @.dt + ' where quoteno ='

with

Select @.SQL = @.SQL + 'quoteapprovaldate = ' + '''' + CAST(@.DT AS VARCHAR(20))+ '''' + ' where quoteno ='

your string would look something like
quoteapprovaldate = 'Apr 5 2004 3:35PM' where quoteno =

Navin|||Sweet - that works great!

Thanks much.

DateTime Format Problem: Setting to 16/Mar/2006 8:50:00 AM

my Current DateTime Format is 01/08/2006 9:15:00 AM

i want to set it to 01/Aug/2006 9:15:00 AM

what parameter will be pass in "SET DATEFORMAT"?

plz help and give me a chance of thanks.

You can't.

Shouldn't be trying to use SQL Server to do presentation formating anyhow.

|||

thanksMotley !

Idon't want to use SQL Server to do presentation formating . I want to fix this format for storage of DateTime data any time. I mean that any time any one tries to insert the DateTime data in my Database, that data should be store in my required format.

|||

Datetimes don't have a "format" (Ok, technically they do, but not a string format). They are stored as the number the days (and fractions there of) since the epoch. It does not store the months, years, hours, minutes, or seconds, let alone store them in any user-specified "format".

The fact that query analyzer/management studio/visual studio presents that value to you in a particular string format is for your benefit as a matter of presentation (AKA presentation formatting). SQL Server does have some capability in being able to convert to/from a datetime and varchar, but it's fairly rudimentary since you really shouldn't be doing presentation formatting in the database.

It's similiar to wanting to store the number of dollars of something into a table and asking how to get an integer field to accept the format "$5,000". It can't. That's not the databases job, that's for the presentation layer of your application to do (Convert $5,000 to 5000).

|||

again Thanks Lot Motley!

I have 2 different machines. Both having the SQL Server 2000 Professional Editon and OS = Windows XP.

One of the machine(M1) displays datetime like 16/Mar/2006 8:50:00 AM

while another machine(M2) has format 16/03/2006 8:50:00 AM

how to set same format for M2 as M1.

|||

As far as how the system displays it, it is probably a Window's setting. Have a look in Regional and Language Options from Control Panel - there is one for Long Formats, and one for Short. I don't think that how it is displayed is controlled by SQL Server, since as the other chap mentioned, SQL Server doesn't store dates in any kind of "format".

Otherwise, you can specify a format from SQL Server, but it will convert it from the datetime object to a varchar. You do this using the CONVERT function:

CONVERT

(varchar(12),p.date_effective,106)

The final parameter, 106 in this case, is the datetime format you want to use. A complete list of them is available at this site:

http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Hope this helps!

|||As dominic mentioned, whatever application you are using to view the data is probably getting your preferred date format from the system settings of the client machine. You can set that in the control panel->Regional and Language Options->Customize->Date(Tab)->Short Date Format->dd-MMM-yyyy|||

yeah this was the solution.

Thanks Motley.

datetime column formula

Hello all:

Using EM to add a column Date_Entered with data type dateTime, what is the syntax to default the date to current date when record is added and to ensure it does not update if the record is modified at some other time in the future. Is it also possible to exclude the time when the column is updated (instead of 4/18/2003 9:32:56 PM the colum would be derived as 4/18/2003)

Is there a publication with listing of all legal suntax used in SQL2000?

Thank youbol has the syntax.

I don't advise using e-m to update the schema.

The sql would be something like

alter table x add dte datetime not null default convert(varchar(8),getdate(),112)

The column is not updated - only defaulted on insert.

If you want it to be set to the current date on update you can do it in a trigger.

Datetimes always include a time - the above will set it to midnight. It is up to you the format in which you display it.

datetime

i'm using datediff to get the elapsed time b/t a timestamp and the current time. at this point i'm putting the answer in minutes, but i would like to format it to be similar to HH:MM.
how do i do this in sql server??
thanks in advance
e3wittselect cast(datediff(mi,'05/28/2004',getdate())/60 as varchar)+':'
+cast(datediff(mi,'05/28/2004',getdate())-(datediff(mi,'05/28/2004',getdate())/60)*60 as varchar)|||SET ANSI_NULLS OFF
SET NOCOUNT ON
GO

if object_id(N'dbo.fn_ElapsedTime') is not null begin
drop function dbo.fn_ElapsedTime
print 'Function dbo.fn_ElapsedTime dropped'
end
go

CREATE function fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null)
returns varchar(40)
as
begin
declare @.d int, @.h int, @.m int, @.s int, @.ms int, @.dif1 int, @.ret varchar(40)
select @.d = 0, @.h = 0, @.m = 0, @.s = 0, @.ms = 0

set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + ':' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end
go

if object_id(N'dbo.fn_ElapsedTime') is not null begin
print 'Function dbo.fn_ElapsedTime created'
end
go|||ok... now it's working just the way i was wanting.

thank you.|||Don't forget about the modulo operator (%). It's hand for converting time values:

select cast(datediff(mi, [TimeStamp], getdate())/60 as int) + ':' + (datediff(mi, [TimeStamp], getdate()) % 60)

dates and Time Comparisons

got a quick question guys.

if i use this to parse the current date to the right side of the time.
right(getdate(),7) - i'll get something like 7:30AM.

i also have Times stored in a column of a table, but as a string not a date time.
it seems to compare okay, but when the time is say 1:30PM and im comparing it if its greater than or equal to (>=)to 7:30AM - it doesnt return.

i think its ignoring the AM/PM Meridian Values and just comparing the numbers.

is there a conversion i could use to do this?
ive tried a military time conversion i found but it converts to hrs,min,milliseconds.
convert(char(8),(convert(datetime,current_timestam p,113)),114)

if anyone knows a good way to do this - i would appreciate it.

thanks again
rikI think i may have a solution to this, but, i probably should make this a UDF.

using military time and reading from the left rather than right :::
select left(convert(char(8),(convert(datetime,'7:33AM',11 3)),114),5)
gives me 07:33 -
which if i compare it to
select left(convert(char(8),(convert(datetime,'1:33PM',11 3)),114),5)

i'll get 13:33 and can compare too 07:33 just fine.

does that sound about right?

datepart

I am trying to use datepart to determine what row in a table a users
hiredate is closest to current system date
example
JOE was hired in Mar 01 2000
I need to get his payrate based off months experience
<12 months
<24 months
<60 months
<120 months
<200 months
Thanks
mike
something like below.
select hiredate, monthsrow from payee, payrategroup where
hiredate,
getdate(),
ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
+
ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
monthsrowHi
CREATE TABLE #Test
(
empl INT NOT NULL PRIMARY KEY,
hiredate DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'20060101')
INSERT INTO #Test VALUES (2,'20060101')
INSERT INTO #Test VALUES (3,'20060409')
INSERT INTO #Test VALUES (4,'20060110')
INSERT INTO #Test VALUES (5,'20060112')
INSERT INTO #Test VALUES (6,'20060120')
INSERT INTO #Test VALUES (7,'20060108')
INSERT INTO #Test VALUES (8,'20060103')
DECLARE @.dt DATETIME
SET @.dt ='20060115' --desired date
SELECT TOP 1 WITH TIES *
FROM #Test WHERE hiredate>'20050101' AND hiredate < DATEADD(day,1,@.dt)
ORDER BY hiredate DESC
<ciojr@.yahoo.com> wrote in message
news:1144550863.151230.197030@.t31g2000cwb.googlegroups.com...
>I am trying to use datepart to determine what row in a table a users
> hiredate is closest to current system date
> example
> JOE was hired in Mar 01 2000
> I need to get his payrate based off months experience
> <12 months
> <24 months
> <60 months
> <120 months
> <200 months
> Thanks
> mike
> something like below.
> select hiredate, monthsrow from payee, payrategroup where
> hiredate,
> getdate(),
> ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
> +
> ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
> monthsrow
>|||Hi Mike,
Can you give the ddls and the expected output. The question seems to be
a bit confusing.|||not what I am looking for.

datediff problems.

Hi all, I have quite a conundrum, at least for me.

I need to get the difference in minutes between the current date and a timestamp. However, I have two timestamp fields. The first one could be NULL (TIMESTAMP_1). The second one is never NULL(TIMESTAMP2). What I want to do is say give me the datediff between the max of TIMESTAMP_1 or TIMESTAMP_2 and the current time.

This is what I tried to select:

datediff(mi,max(isnull(TIMESTAMP_1,TIMESTAMP_2)),g etdate())

However, it grabs TIMESTAMP_1 if it's there and if not then it grabs TIMESTAMP_2. How can I tell it to take the max of both?

This is Sybase ASE 12.5.

Thanks for any help.well this is using sql server so code might be a bit different but same concept should be able to be used.

case when timestamp1 > timestamp2 then DATEDIFF(mi, timestamp1, getdate())
when timestamp1 < timestamp2 then DATEDIFF(mi, timestamp2, getdate())
ELSE DATEDIFF(mi, timestamp1, getdate()) END

DateDiff

I am trying to select records from whatever the current date would be and 12 months before whatever the current date is. How would I go about doing this. The table that I am trying to do this with has a year column and a month column.

I was playing with the date diff function, but I can only get dates from the specified date range. I need it to be where if I run it tomorrow, it will get that day and everything within the last 12 months.The concept of what a month is, is funny...you'd be better of if you could pick a fixed number of days

It's like month is nondetermenistic, where days are determenistic..

Never read that anywhere I don't think (damn I hate when that happens)

USE Northwind
GO

CREATE TABLE myTable99(myMonth99 int, myDay99 int, myYear99 int)
GO

INSERT INTO myTable99(myMonth99, myDay99, myYear99)
SELECT 1,1,2003 UNION ALL
SELECT 2,1,2003 UNION ALL
SELECT 3,1,2003 UNION ALL
SELECT 4,1,2003 UNION ALL
SELECT 4,15,2003 UNION ALL
SELECT 4,30,2003 UNION ALL
SELECT 5,1,2003 UNION ALL
SELECT 6,1,2003 UNION ALL
SELECT 7,1,2003 UNION ALL
SELECT 8,1,2003 UNION ALL
SELECT 9,1,2003 UNION ALL
SELECT 10,1,2003 UNION ALL
SELECT 11,1,2003 UNION ALL
SELECT 12,1,2003
GO

SELECT * FROM myTable99
WHERE DATEDIFF(mm,
CONVERT(datetime,
CONVERT(varchar(4),myYear99)
+'/'+CONVERT(varchar(4),myMonth99)
+'/'+CONVERT(varchar(4),myDay99))
,GetDate()) >= 12
GO

DROP TABLE myTable99
GO|||Yeah, I see what you mean with the moth thing being funny. I figured out another way where I can do it. If I leave the date fixed where it displayes the year, month and day, I can accomplish what i have set out to do. This is what worked for me.

select * from podinrh
where date_rcvd between getdate() - 365 and getdate()
order by date_rcvd

I will also try out the script that you have provided me with Brett. Thanks for all the help you all provide.|||Yeah thats days...

Works much better

SELECT * FROM myTable99
WHERE DATEDIFF(dd,
CONVERT(datetime,
CONVERT(varchar(4),myYear99)
+'/'+CONVERT(varchar(4),myMonth99)
+'/'+CONVERT(varchar(4),myDay99))
,GetDate()) >= 365
GO|||Originally posted by estefex
I will also try out the script that you have provided me with Brett.

You know to just cut and paste it in to QA right?|||Brett, It works fine too. I will have to see what format they are going to want to stick to. I am thinking they are going to want it with the date broken bown into columns like you did. I appreciate you help.|||The Devil is in the details. To handle leap years, you might want to use this instead:

select * from podinrh
where date_rcvd between dateadd(year, -1, getdate()) and getdate()
order by date_rcvd

Also remember that the BETWEEN operator is inclusive. If your data is stored as whole dates (without the time of day), then you may end up excluding data from the first day or including an extra 24 hours at the end.

For date comparisons, I use this instead of BETWEEN:

select * from podinrh
where date_rcvd > dateadd(year, -1, getdate()) and date_rcvd <= getdate()
order by date_rcvd|||Thanks for the tipp because i was useing the time with the date as well. The number of records returned is a but greater than when I used the between statement.

datediff

hi, Im tryinf to fix in to the function date different. One of the date is i
n
the declared variability and the other is current day
why
select datediff(hh,@.datetime,getdate())
doesnt work in the function?
It responds : INVALID USE OF GETDATE WITHIN A FUNCTION
Thanks in advanceThats one of the limitations in functions, you can=B4t use getdate(). A
common solution is to pass the current date as a paramter to the
function.
HTH, Jens Suessmeyer.|||Please, can you give a short example?
Jens p_?e:

> Thats one of the limitations in functions, you can′t use getdate(). A
> common solution is to pass the current date as a paramter to the
> function.
> HTH, Jens Suessmeyer.
>|||Define an extra in parameter for your function, and pass if the current date
time in that parameter
when you call the function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"pietro" <pietro@.discussions.microsoft.com> wrote in message
news:66C97A6C-932A-43C7-AB67-C816DB330C5A@.microsoft.com...
> Please, can you give a short example?
>
> Jens p_?e:
>|||Sure:
CREATE FUNCTION SomeFunction
(
@.SomeDateparam DATETIME
)
(...)
Call: SELECT dbo.SomeFunction(GETDATE())
HTH, Jens Suessmeyer.

dateadd

Just want to double check this.

To add 30 days to the current date in a stored procedure using SQL Server should be this:

DATEADD(day, 30, GETDATE())

Right?

Thanks,

Zath

Yes, I will do the same thing.