query by date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query by date

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-28 : 09:17:43

i have a field called
expdate which is like 0212 - so 2 digit month and 2 digit year

I want to do an update statement
and update c set ccvalid=0 where
the month and year are in the past ?

any easy way to convert a varchar of 0212 to a date 20120230

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-28 : 11:09:42

[code]
select expdate, dateadd(month, 1, convert(datetime, '20' + right(expdate, 2) + left(expdate, 2) + '01')) - 1
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-28 : 11:24:41

I get an error

Conversion failed when converting date and/or time from character string.

I added at the end

where CardExp is not null and LEN(cardexp)=4
to make sure it is a 4 digit number but I still got this error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-28 : 11:29:55

you probably have bad data in there.

use isdate() to list out these data

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-28 : 11:36:26

isdate where?
after I convert it?
as before I convert it's not a date it's just a string of mmyy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-28 : 18:44:59

after you form the date string


ISDATE( '20' + right(expdate, 2) + left(expdate, 2) + '01' )

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 05:35:33

no something like 0909 is converting to 1900-02-01 00:00:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-29 : 05:41:42

quote:
Originally posted by esthera

no something like 0909 is converting to 1900-02-01 00:00:00.000

Really ?


SELECT 	expdate, dateadd(month, 1, convert(datetime, '20' + right(expdate, 2) + left(expdate, 2) + '01')) - 1,
	ISDATE( '20' + right(expdate, 2) + left(expdate, 2) + '01' ) as valid_date
FROM	(
		select	expdate = '0909'
	) t

expdate                                                        valid_date  
------- ------------------------------------------------------ ----------- 
0909    2009-09-30 00:00:00.000                                1

(1 row(s) affected)

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-29 : 05:45:12

quote:
Originally posted by esthera

i have a field called
expdate which is like 0212 - so 2 digit month and 2 digit year

I want to do an update statement
and update c set ccvalid=0 where
the month and year are in the past ?

any easy way to convert a varchar of 0212 to a date 20120230

You need to know why you should always use a proper DATETIME datatype.
Also what does 20120230 mean? February 30?

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 06:32:04

the problem is my data is sometimes not correct

anyway I can do the date convert only where the date is a date (and otherwise validdate would be set to 0)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-29 : 06:42:01

yes. You can use ISDATE() to verify first before converting.

KH
[spoiler]Time is always against us[/spoiler]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 06:47:45

how would I do it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-08-29 : 07:03:11

quote:
Originally posted by esthera

how would I do it?

First try it yourself and post here if it doesnot work.

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-08-29 : 12:42:30

ok I got this working

SELECT cardexp, dateadd(month,1,convert(datetime, '20' + right( cardexp, 2) + left( cardexp, 2) + '01')), '20' + right( cardexp, 2) + left( cardexp, 2) + '01'

FROM customers
where ISDATE('20' + right( cardexp, 2) + left( cardexp, 2)+ '01')=1

but if I try

SELECT cardexp, dateadd(month,1,convert(datetime, '20' + right( cardexp, 2) + left( cardexp, 2) + '01')), '20' + right( cardexp, 2) + left( cardexp, 2) + '01'

FROM customers
where ISDATE('20' + right( cardexp, 2) + left( cardexp, 2)+ '01')=1
and dateadd(month,1,convert(datetime, '20' + right( cardexp, 2) + left( cardexp, 2) + '01'))>GETDATE()

I still get an error as it can't convert it - what's the way around this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources