using a getdate in a query

forgot to also mention that YES col1 is a Datetime formated field

Of course i needed to us the datename on the col1 as well! :)

achally i needed to use

where  datename(MONTH,col1) = datename(MONTH,(getDate()))  becase acahlly the field is not a datetime formated feild! nice! :)

so if say i wanted to run the query so that is showed last moths can that be done with a -1 somewhere?

yes, you can use the dateadd function and just subtract one month.

format
DATEADD ( datepart , number, date )

so for you

datename(month,DATEADD ( mm , -1, getdate() ) )

SWEET!

And for 100 more points( i promise i will add them on)  

Is it possible that i can convert a naming convertion in to a reconised format?

as it stand i have a month filed displaying data as a 3 char date field

 IE
april = apr
December - dec

I dont think this is a SQL standard is it? maybe a comaristion and replace? this part is way over my head!

In your col1 field? For display? If so, this is a datetime field and you can display it how you want in almost any combination of ways. The storage is in the background and the display is just formating.

For this, use the convert or cast function.
for example,
select convert(varchar(10),getdate(),101) returns 04/29/2004
select convert(varchar(11),getdate(),106) returns 29 Apr 2004
select convert(varchar(8),getdate(),112) returns 20040429
etc

You can display however you want, the internal storage of a datetime field is an integer.

hai,
iam not an expert in sql, but something like this will work for u....
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=xNCEb.3006%24cM1.921220%40twister.nyc.rr.com&rnum=1&prev=/groups%3Fq%3Dsql%2520query%2520current%2520date%2520month%2520year%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26sa%3DN%26tab%3Dwg

its like

Select col1, col2, col3
from table1
Where MONTH(col1) = MONTH(CURRENT_TIMESTAMP);

but iam not sure...
u can look at the link and do it...

all the best...

Some warnings here...

After a year you will run in trouble with this setup...

MONTH(col1) returns the Same for every Feb. in every year...

Also its not "nice" performance wise to put a function on a Field and compare it to the result of another function...

SQL is not "smart" and "knows" what you want to achieve. He will just plug ALL rows into the function and compare ALL results (Table or Index - Scan = EVIL!!!)

For the best result of your querry do this...

Where col1 between cast(year(getdate())as varchar(4)) + case
            when month(getdate()) = 1 then '12'
            else right( '00' + cast(( month(getdate()) -1) as varchar(2)),2)
            end + '01' and dateadd(month,1,cast(year(getdate())as varchar(4)) + case
            when month(getdate()) = 1 then '12'
            else right( '00' + cast(( month(getdate()) -1) as varchar(2)),2)
            end + '01') -1

This way you can properly use the index on the date field... :)

P.s. Take a look at the execution plan for both querries...

The querry above was for the complete last month...

Am i missing this or is these answer bending toward the date being in a 3 char format or a number field?

i think maybe i should post this as a sparate posting.


Chris you got points!

Internally, dates are stored as integers (numbers), you can compare and display them any way that you want, as is requried for your app. Just store them as datetime and you're safe.

What i mean is that i have a flat file data feed that gets import via dts once a day.

the way that the flat file has the month set is as a 3 character field ( IE fed,dec,jan etc) now at the time of data import i cant import this into a datetime filed as i cant ransform this into a format that will be accepted.

so when i call it is in my recordset their any way of doing this ?

I am thinking that i will have to have a spearte table that has 2 coloums

abrev    full

JAN        January
FEB       Febuary
MAR      March
ETC

And then using this to convert the date to full name and using that in the datename part of the query.

I know that thi wil work but i wanted to see if their is any other way of achiving ths goal.

Can you let me know the full format of the date field
is it 01 jan 2004 or 01jan2004 or ???

Yes you can import it with DTS directly into a datetime field, you just need to use the properties tab on the copy column for the date

I've had to do this when I had data coming from many sources who all formated dates differently (some not recognized by sql as dates without some help) so you should be okay, its just a bit harder to find.

it is simply JAN

their is another coloum in the flat file called year that is 2004 but i can import this fine but if i could impot them into the same datetime filed thenthat would double sweet!

let me think and play and get back to you. so you have just year and month, no date. not impossible.....
what format is the file (delimited, fixed?)