Solved

using a getdate in a query

Posted on 2004-04-29
15
702 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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

820 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