-
Jan 23rd, 2007, 03:31 AM
#1
Thread Starter
Hyperactive Member
[RESOLVED] comprehensive DatePart use. why no go?
heya all
trying to use the + operator, to tie 2gether two Date parts of one date,
into 1 field .
my query so far:
VB Code:
select isnull(datepart(mm,sdate),'')+' '+isnull(datepart(yy,sdate),'') as date
,count(unit) ts
from saleslog
WHERE sdate BETWEEN '01-01-2006 00:00:00' AND '1-31-2007 23:59:59'
GROUP BY all isnull(datepart(mm,sdate),'')+' '+isnull(datepart(yy,sdate),'')
ORDER BY isnull(datepart(mm,sdate),'')+' '+isnull(datepart(yy,sdate),'')
other than selecting two datepart (1 for year,1 for month),
how can i get a "DATEPART(mm+yy, sdate)"
tnx in advance for replying.
gooday,
-j
-
Jan 23rd, 2007, 03:41 AM
#2
Re: comprehensive DatePart use. why no go?
what is the database that you are using....
If an answer to your question has been helpful, then please, Rate it!
Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.
-
Jan 23rd, 2007, 04:10 AM
#3
Thread Starter
Hyperactive Member
Re: comprehensive DatePart use. why no go?
-
Jan 23rd, 2007, 04:13 AM
#4
Re: comprehensive DatePart use. why no go?
try
Code:
Select Month(sDate) + Year(sDate) As NewDate From YourTableName
If an answer to your question has been helpful, then please, Rate it!
Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.
-
Jan 23rd, 2007, 06:06 AM
#5
Thread Starter
Hyperactive Member
Re: comprehensive DatePart use. why no go?
Originally Posted by ganeshmoorthy
try
Code:
Select Month(sDate) + Year(sDate) As NewDate From YourTableName
cool. tnx for a quick reply man.
didnt work, functions Month(Date) and Year(Date) , return integers.
so for Jully,2006 comes back= 2013. (2006+7).
managed to get around it though, Casted the fields to varchar... like so
VB Code:
select cast(Month(sdate) as varchar)+'\'+cast(Year(sdate) as varchar) as slDate,
month(sdate) M, year(sdate) Y, count(unit) from saleslog
WHERE sdate BETWEEN '02/01/2006 00:00:00' AND '01/23/2007 23:59:59'
group by all cast(Month(sdate) as varchar)+'\'+cast(Year(sdate) as varchar),year(sdate),month(sdate)
order by year(sdate) desc, month(sdate) desc, cast(Month(sdate) as varchar)+'\'+cast(Year(sdate) as varchar)
thanks again for your help on this.
regards,
-j
-
Jan 23rd, 2007, 06:43 AM
#6
Re: comprehensive DatePart use. why no go?
You must convert the datetime columns to varchar using convert or cast functions.
I would recommend convert since it gives you the possibility to choose what format the datetime value should be converted to.
Take a look i BOL for CONVERT. I could of course tell you how to do it, but you'll learn more by doing it yourself. All you need is a push in the correct direction -> CONVERT...
-
Jan 23rd, 2007, 07:01 AM
#7
Re: comprehensive DatePart use. why no go?
Im sorry, missed that cast part...anyway, its nice to know that it helped you...
If an answer to your question has been helpful, then please, Rate it!
Have done Projects in Access and Member management systems using BioMetric devices, Smart cards and BarCodes.
-
Jan 23rd, 2007, 08:16 AM
#8
Re: comprehensive DatePart use. why no go?
MS SQL SERVER gives us two functions - DATEPART - which returns integers as you discovered...
And DATENAME - which returns strings - that would be the method to use.
Of course this will give you a MAY instead of 05 for month.
Otherwise look at CONVERT - which is a nice way to change a date to a string and then you can LEFT() off of that or RIGHT() off of that return and get what you want.
-
Jan 24th, 2007, 12:25 AM
#9
Thread Starter
Hyperactive Member
Re: comprehensive DatePart use. why no go?
Originally Posted by szlamany
MS SQL SERVER gives us two functions - DATEPART - which returns integers as you discovered...
And DATENAME - which returns strings - that would be the method to use.
Of course this will give you a MAY instead of 05 for month.
Otherwise look at CONVERT - which is a nice way to change a date to a string and then you can LEFT() off of that or RIGHT() off of that return and get what you want.
very good morning sZlamany,
tnx for chiming on this 1 and for suggestions.
nice day,
regards,
-j.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|