How to show only month and year in a date field
-
I am working on a new data project that has a lot to do with credit card expiry date. Any idea about how to do that in SQL server 2000? I need the users to enter only month and year for the field. I am using access as a front data entry.
thank you
-
I would look into the functions: datediff, datepart and dateadd in the books online. This is pretty all you'll be needing.
Any specific problem you need help with?
-
When the user enters the expiry date as e.g 05/06 in the form, this is converted into 05/06/2005?
-
How is the expiration date kept on the server?
-
The data type for that field is smalldatetime but it even would be better to keep it as month year only, however this data type format is not available
-
Hmm, I think that having it as a date is still the best option.
You can recreate the date using the month and year like so :
Declare @Year as int --parameter of a stored proc
Declare @Month as int --parameter of a stored proc
Declare @Date as smalldatetime
set @Year = YEAR(GetDate())
set @Month = Month(GetDate())
set @Date = dateadd(YYYY, @Year - 1900, (dateadd(MM, @Month - 1, 0)))
Select @Date
-
Btw dates often make very good indexes... maybe not quite as good in this case since there are only 12 values/year but I think it's still better than a char.
-
Where to put all that code?
-
Have you ever used sql server??
-
Yes I have been using it for more than a year now
-
You can create a function to return the formated date since it's probabely gonna be reused later.
Otherwise it's gonna depend on the rest of the application... You're pretty much asking me how to build a house... I know how to but I'm not gonna do all the work for you
. Keep posting here if you have more specific questions. -
thank you
-
Maybe this will give you some ideas.
create procedure dbo.validatedate @inputdate char(6)
as
declare @31st datetime
set @31st = '19001031'
select dateadd(month, datediff(month,@31st,cast(@inputdate+'01' as datetime)),@31st)
--your other stuff goes here
return
go
exec dbo.validatedate '200501'
drop procedure dbo.validatedate
But as Remi said, TANSTAAFL. And if you want good suggestion you have to be a bit more specific in your questions.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
A previous post says that dates make good indexes. I tend to disagree. At the end of the day a date is stored as a number. Because types of smalldatetime and datetime allow great granularity there index values are quite high and therefore require more checking. I would store the expiry date as an integer in the form YYYYMM. I would then create several functions to validate the users input using the CONVERT function e.g. have the function accept year and month and put the day as 1 and check that it is a valid date. I would spell out the code but I think you get the idea ... it is only a few lines.
Cheers.
-
"A previous post says that dates make good indexes. I tend to disagree."
I can't say that this comment didn't make me jump from my seat. But apprently you are correct. I ran this test and found out that the int version version runs about 20% faster :
Declare @DateStr as smalldatetime
Declare @DateChk as smalldatetime
Declare @DateInt as int
Declare @Year as int
Declare @Month as int
Declare @Date as smalldatetime
Declare @i as int
Declare @Loops as int
Declare @Start as DateTime
set @Year = YEAR(GetDate())
set @Month = Month(GetDate())
set @Loops = 50000
set @i = 0
set @DateStr = CAST(@Year as varchar(4)) + '/' + CAST(@Month as varchar(2)) + '/01'
set @DateChk = @DateStr
set @Start = GetDate()
while @i < @Loops
begin
--set @Date = dateadd(YYYY, @Year - 1900, (dateadd(MM, @Month - 1, 0)))
if @DateStr = @DateStr
set @i = @i + 1
else
set @i = @i + 1
end
Select DateDiff(MS,@Start, GetDate()) as "Datetime"
set @i = 0
set @DateInt = cast(cast(@Year as varchar(4)) + cast(@Month as varchar(2)) as int)
set @Start = GetDate()
while @i < @Loops
begin
--set @Date = Convert(datetime, CAST(@Year as varchar(4)) + '/' + CAST(@Month as varchar(2)) + '/01')
if @DateInt = 200505
set @i = @i + 1
else
set @i = @i + 1
end
Select DateDiff(MS,@Start, GetDate()) as "int"
datetime version ran in 1470 ms
int version ran in 1233 ms
I've always thaught that dates make the best indexes because of the high granulity but obviously in this case, the datetime index wouldn't be selective enough to beat the int version.
Always something else to learn
, thanx Peter.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply