July 4, 2003 at 6:44 am
As per project requirement i need to convert
string 27/02/2003 to date .Please suggest me some way as I am unable to compare this string with other date values
July 4, 2003 at 6:45 am
Use this function.
CREATE FUNCTION dbo.ConvertDate
(
@Date Varchar(10)
)
RETURNS Varchar(100) AS
BEGIN
Declare
@vDay Char(2),
@vMonth Char(2),
@vYear Char(40),
@vMonthName Varchar(100),
@vResult Varchar(100)
/*
If ISDATE(@Date) = 0
Begin
Select @vResult = '10 Jan 1900'
Return @vResult
End
*/
Select @vDay = Substring(@Date, 1, 2)
Select @vMonth = Convert(Int, Substring(@Date, 4, 2))
Select @vYear = Substring(@Date, 7, 4)
Select @vMonthName =
Case @vMonth
When 1 Then 'Jan'
When 2 Then 'Feb'
When 3 Then 'March'
When 4 Then 'April'
When 5 Then 'May'
When 6 Then 'June'
When 7 Then 'July'
When 8 Then 'August'
When 9 Then 'September'
When 10 Then 'October'
When 11 Then 'November'
When 12 Then 'December'
End
Select @vResult = RTrim(LTrim(@vDay)) + ' ' + RTrim(LTrim(@vMonthName)) + ' ' + RTrim(LTrim(@vYear))
Return @vResult
END
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 4, 2003 at 7:06 am
crispin
thanks for immediate response but my problem is not solved .
see i have copied my column hear
i need to compare each date in this column
i will show you my querry
select *
from table
where Billeddate between '01/01/03 and '03/31/03'
since data type of billeddate is a char ..the query is no giving me any answer
BillingDate
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
28/02/2003
28/02/2003
28/02/2003
28/02/2003
28/02/2003
28/02/2003
28/02/2003
28/02/2003
28/02/2003
July 4, 2003 at 7:11 am
You can use:
select *
from table
where Billeddate between dbo.ConvertDate('01/01/03') and dbo.ConvertDate('03/31/03')
Two problems I can see here.
1) Your years are two digits. You'll have to make a change to the function to support two digit years.
2) Change the return type of the function to DateTime. I kept it as a varchar for a couple reasons. You could also (Not recommended) cast the result of the function.
eg: Cast(dbo.convertdate('ffff') as DateTime) This wuld be a bit slower than the first option.
Would this solve your problem?
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 4, 2003 at 7:25 am
crispin
thanks again ,before looking for help i have tried this
cast(billingdate as datetime) but it is giving me a error
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
so unable to convert string billingdate to string
if you wish i can change my querry
to
select *
from table
where billingdate between '01/01/2003' and '03/31/2003'
this query is working perfectly fine as datatype of form date is datetime
select empno
from tempallocationdata
where fromdate between '01/01/2003' and '03/31/2003'
July 4, 2003 at 7:33 am
The reason you getting an overflow is SQL is "confused" by the date.
You have 03/31/2003. SQL is trying to convert 03 to the day, 31 to the month. There are not 31 months in the year 🙂
The function return 01 mar 2003. This SQL cannot get confused.
You could also use
SET DATEFORMAT dmy
at the top of your proc. This tells SQL that the date format is gonna be day month year.
(dd/mm/yyyy)
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 4, 2003 at 7:46 am
sir
I clearly specified in my earlier mails is
Billingdate is a char type .I want to compare it with two date values
once again writing my query
select *
from table
where Billingdate between date1 And date2
i am not bothering about date1 and date2
the part troubling me is char billingdate
i want tot convert this billing date to date type .How can i use your function in my querry ..plz explain
BillingDate
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
31/01/2003
July 4, 2003 at 8:01 am
If you want to convert a dd/mm/yyyy string to a datetime value use something like:
select convert(datetime, '27/02/2003', 103)
or
select convert(datetime, BillingDate, 103)
The "103" tells the CONVERT function that the string is dd/mm/yyyy format.
Cheers,
- Mark
July 4, 2003 at 8:16 am
i have modified your function and it is working perfectly fine
now i m using a query like this
select *
from table
where dbo.condate(billingdate) between date1 and date2
CREATE FUNCTION dbo.ConDate
(
@Date Varchar(10)
)
RETURNS datetime AS
BEGIN
Declare @vDay int,@vMonth int,@vYear int,@vResult datetime
Select @vDay = Convert(Int,Substring(@Date, 1, 2))
Select @vMonth = Convert(Int, Substring(@Date, 4, 2))
Select @vYear = Convert(Int,Substring(@Date, 7, 4))
Select @vResult = RTrim(LTrim(@vDay)) + '/ ' + RTrim(LTrim(@vMonth)) + '/' + RTrim(LTrim(@vYear))
Return @vResult
END
once again thanks a lot
July 4, 2003 at 9:56 am
hi mccork,
great My problem is solved with out using a function .I have provided my final query below ..
will you have any solution to convert string in format 29.02.2003 to datetime
set dateformat dmy
select *
from maneffort M
where (select convert(datetime,m.billingdate , 103) )between '31/01/2001'and '31/03/2003'
July 4, 2003 at 2:54 pm
SQL Books Online has all the convert functions. In the dd.mm.yyyy case a "104" tells CONVERT that it's a "German" style date with a 4-digit year.
select convert(datetime, '27.02.2003', 104)
For your posted solution, it's a matter of preference, but I would go without the SET DATEFORMAT, and supply unambiguous date formats in the criteria. Eg.
select *
from maneffort M
where convert(datetime,m.billingdate , 103) between '2001-01-30' and '2003-03-30'
Cheers,
- Mark
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply