Solved

using a getdate in a query

Posted on 2004-04-29
15
685 Views
Last Modified: 2010-08-05
Is it possible, i am sure it is. that i can us a combination of a getdate and datename within a query to be able to achaive a where command?

say that i have a query like this

Select col1, col2, col3
from table1
Where col1 = datename(MONTH,(getDate()))

I want to be able to always have the current month or year in the query with out having to pas the value into the query.

Willa
0
Comment
Question by:willa666
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 1

Author Comment

by:willa666
ID: 10953352
forgot to also mention that YES col1 is a Datetime formated field
0
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 125 total points
ID: 10953368
I think you would have to change it to

where  datename(col1) = datename(MONTH,(getDate()))

if col1 is a date field. Otherwise you will be trying to make a specific date = 'April' (if you ran it today)
0
 
LVL 1

Author Comment

by:willa666
ID: 10953446
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?
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10953469
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() ) )
0
 
LVL 1

Author Comment

by:willa666
ID: 10953516
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!
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10953612
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.
0
 
LVL 32

Expert Comment

by:ldbkutty
ID: 10953681
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...
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 11

Expert Comment

by:rdrunner
ID: 10957593
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... :)
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 10957614
P.s. Take a look at the execution plan for both querries...

The querry above was for the complete last month...
0
 
LVL 1

Author Comment

by:willa666
ID: 10958843
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!
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10960863
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.
0
 
LVL 1

Author Comment

by:willa666
ID: 10960965
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.

0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10961013
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.
0
 
LVL 1

Author Comment

by:willa666
ID: 10961380
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!

0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10961389
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?)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now