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

General SQL Server Forums

New to SQL Server Programming

date data

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-11 : 16:25:22

I have the data in my column CHDEFAULTLOGIN like so

zzz100107RILEYM4
zzz100107Russol
zzz100107siekraus
zzz100107wangw2
zzz100108cassam1
zzz100108gascok
zzz100108INTCOLMJ
zzz100108malirr

I want to pull only data that are older than 30days past. For eg; today is 11/11/2008 I want anything older than 10/10/2008. The data has " either zzz or zz to disable with date like mmdtyear" I want to just pull the data older than 30days and delete them. How would i pull just those that i want.

A sample query would help.

I tried (CHDEFAULTLOGIN LIKE '%zzz10%')and (CHDEFAULTLOGIN LIKE '%2008%')

Thanks,
Maachie

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 16:35:37

how about

CHDEFAULTLOGIN LIKE '%zzz10__08%'

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-11-11 : 20:58:59

You need 2 different things here. You need a UDF that would seperate the numbers and then convert it into date. Here is the function for it.

--Function that searches a string and seperates numbers out of it
Create function [dbo].[SeperateNumsInString](@InTheString varchar(5000))
returns varchar(5000)
as
begin
while patindex('%[^0-9]%',@InTheString)>0
Set @InTheString = rtrim(ltrim(replace(@InTheString,substring(@InTheString,patindex('%[^0-9]%',@InTheString),1),'')))
return @InTheString
end

/*
The sql for it using your own test data except for the last row as you havent provided any data for less than 30 day date.
*/

declare @Login table (CHDEFAULTLOGIN varchar(100))
insert into @Login
select 'zzz100107RILEYM4' union
select 'zzz100107Russol' union
select 'zzz100107siekraus' union
select 'zzz100107wangw2' union
select 'zzz100108cassam1' union
select 'zzz100108gascok' union
select 'zzz100108INTCOLMJ' union
select 'zzz100108malirr' union
select 'zzz102108malirr'

select CHDEFAULTLOGIN,
Numbers = dbo.SeperateNumsInString(CHDEFAULTLOGIN),
Date = convert(datetime,'20'+right(substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),2)+left(right('00'+substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),6),4)),
Newdate =
DateAdd(day, -30, convert(datetime,'20'+right(substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),2)+left(right('00'+substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),6),4)))
from @Login
where convert(datetime,'20'+right(substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),2)+left(right('00'+substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),6),4))
> dateadd(day,-30,getdate())

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 08:01:17

hanbingl

Thanks for your response. I want older than
CHDEFAULTLOGIN LIKE '%zzz10__08%' from
zzz100107RILEYM4
zzz100107Russol
zzz100107siekraus
zzz100107wangw2
zzz100108cassam1
zzz100108gascok
zzz100108INTCOLMJ
zzz100108malirr

which means i should be able to pull out for eg these:
zzz100107RILEYM4
zzz100107Russol
zzz100107siekraus
zzz100107wangw2

How would i do that?
Thanks,
Maachie

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 08:25:17

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 08:47:39

Hi Kumar_Anil

I tried this UDF funtion. I did not understand what does this pc below required for?

Newdate =
DateAdd(day, -30, convert(datetime,'20'+right(substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),2)+left(right('00'+substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),6),4)))

This is the result set i got from running the function

CHDEFAULTLOGIN Numbers Date Newdate
zzz102108malirr 102108 10/21/08 09/21/08

Also, i need all records prior to 30days.
Thanks,
Maachie

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 08:49:43

Hi sakets_2000

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 08:59:42

Then you might have data which doesn;t follow the format you mentioned.

Run this and post the output,

select * from test
where
isdate('20'+ substring(replace(CHDEFAULTLOGIN ,'z',''),5,2) + left(replace(CHDEFAULTLOGIN ,'z',''),2)+substring(replace(CHDEFAULTLOGIN ,'z',''),3,2))<>1

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 09:09:05

Can you please explain what this pc of code does?

isdate('20'+ substring(replace(CHDEFAULTLOGIN ,'z',''),5,2) + left(replace(CHDEFAULTLOGIN ,'z',''),2)+substring(replace(CHDEFAULTLOGIN ,'z',''),3,2))<>1

Thanks,
Maachie

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 09:22:34

Some data there isn't in the format you mentioned.

It could be because you might have something other than "z" stuffed in the beginning, or may be somedata which doesn't suit mmddyy format like you mentioned.

The query I gave you will bring out cases like these. Isdate is a function which would return you 1 if it can be converted to datetime, 0 otherwise.

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 09:29:40

So in my data there are old data and i want everything prior to 30days data. what i mean by that is today is 11/13/08, i want everything prior to 10/13/08. Does your query bring that? I understand that the data also has z, zz, zzz before the mm/dt/year.

Thanks,
Maachie

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 10:19:23

select * from <yourtable>
where
convert(datetime,'20'+ substring(replace(CHDEFAULTLOGIN ,'z',''),5,2) + left(replace(CHDEFAULTLOGIN ,'z',''),2)+substring(replace(CHDEFAULTLOGIN ,'z',''),3,2))
<'20081001'

The above should bring you that. But since you're getting an error. I asked you to run the below query to find out why it was erring.
Can you run it and post some data that it fetches, please .

select CHDEFAULTLOGIN from test
where
isdate('20'+ substring(replace(CHDEFAULTLOGIN ,'z',''),5,2) + left(replace(CHDEFAULTLOGIN ,'z',''),2)+substring(replace(CHDEFAULTLOGIN ,'z',''),3,2))<>1

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 11:17:13

and CHDEFAULTLOGIN not LIKE '%zzz10__08%' and CHDEFAULTLOGIN not LIKE '%zzz11__08%'

is bringing me excluding for the month Oct and Nov. I get about 6193 rows

But what I wanted is everything prior to 30days.

WHERE (CHDATA <> '') AND (CHSERIALNUM = '') AND (CHDEFAULTLOGIN LIKE 'zz%')
and isdate('20'+ substring(replace(CHDEFAULTLOGIN ,'z',''),5,2) + left(replace(CHDEFAULTLOGIN ,'z',''),2)+substring(replace(CHDEFAULTLOGIN ,'z',''),3,2))<>1

This brings only 131 rows.

Thanks,
Maachie

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 11:40:12

can you post some data that you got out of that query ?>

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 11:50:01

From this, I get the data below

SELECT TOP (100) PERCENT ITOKENNUM, BENABLED, CHSERIALNUM, DATEPIN, DATEENABLED, DATETOKENASSIGNMENT, DATELASTLOGIN, IUSERNUM,
CHLASTNAME, CHFIRSTNAME, CHDEFAULTLOGIN, CHKEY, CHDATA, iReplacingTokenNum, iType
FROM dbo.ACE_USERS
WHERE (CHDATA <> '') AND (CHSERIALNUM = '') AND (CHDEFAULTLOGIN LIKE 'zz%')
and CHDEFAULTLOGIN not LIKE '%zzz10__08%' and CHDEFAULTLOGIN not LIKE '%zzz11__08%'
ORDER BY CHDEFAULTLOGIN

CHLASTNAME CHFIRSTNAME CHDEFAULTLOGIN
ZZZ ZZZZ ZZ
Chen Zhanling zz01042008CHENZ3
Honore Sandrine zz01102008honors
Colak Kristijan zz01312008COLAKK
Shewchuk April zz02072008INTSHEWA
Galligan Sheila zz030308GALLIS
Disco Greg zz041007DISCOG
Matteson Timothy zz043008mattet1
Eden Gregory zz050707EDENG
Makeeva Irina zz070708MAKEEI
Balasubramanian Muthuvadivel zz081707TCSBALAM
Steele Greg zz090706STEELG
Walker Leon D zz122006WALKEL1
Wu Yuan zzz002222008WUY6
Turner Allison zzz01/02/2008TURNEA
ball michael zzzzBALLM112-18-06-Bulk
Barreras Nathan zzzzbarren2-28-bulk
Baugh Michael zzzzBAUGHM12-28-bulk
Bebie Glenn zzzzBEBIEG2-28-bulk
Bell Thomas zzzzBELLT2-28-bulk
GALLAGHER JOSEPH zzzzBENGALLJ12-18-06-Bulk
Moylan David zzzzBENMOYLD12-18-06-Bulk
Hebert Matthew zzzzwrongly craeted

From this the data i get is:
SELECT TOP (100) PERCENT ITOKENNUM, BENABLED, CHSERIALNUM, DATEPIN, DATEENABLED, DATETOKENASSIGNMENT, DATELASTLOGIN, IUSERNUM,
CHLASTNAME, CHFIRSTNAME, CHDEFAULTLOGIN, CHKEY, CHDATA, iReplacingTokenNum, iType
FROM dbo.ACE_USERS
WHERE (CHDATA <> '') AND (CHSERIALNUM = '') AND (CHDEFAULTLOGIN LIKE 'zz%')
and isdate('20'+ substring(replace(CHDEFAULTLOGIN ,'z',''),5,2) + left(replace(CHDEFAULTLOGIN ,'z',''),2)+substring(replace(CHDEFAULTLOGIN ,'z',''),3,2))<>1

CHLASTNAME CHFIRSTNAME CHDEFAULTLOGIN
Pothapu Jagadish zzzpothaj11092006_BULK
Greenplate Carrie zzz61406GREENC5_bulk
Sharma Rohan zzzaccsharr11092006_BULK
Liu Andy zzz12/30/2007liua1
Ahmed Mohammed zzz02/01/08ahmedm4
Kimball Glenda zzzkimbag11092006_BULK
Nayak Bharat zzzzALLNAYAB12-18-06-Bulk
Nagaraja Kumar zzz0690108nagark
Suram Karunakar zzzzALLSURAK1-18-07-bulk
Goswami Richa zzzallgoswr11092006_BULK
Nandini Uma zzznandiu11092006_BULK
Lammert Marco zzzLAMMEM11092006_BULK
Dydek Jerry zzzdydekj11092006_BULK
Cheng Allan zzz13007chenga1
Allsup Kerry zzzallsuk11092006_BULK
Nagel Christopher zzz-11-21-2007-idmcleanupNAGELS
Lim Hong zzz04007lima
Perry Mark zzzperrym11092006_BULK
Picherit Sandrine zzzdnbpiches11092006_BULK
Dessoubret Gilles ZZZ270408dessog

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 12:21:51

right, the first row there --"zzzpothaj11092006_BULK", Does this follow the rules you mentioned earlier ??
Its got more than 'zzz' or 'zz' coming, Its got 'zzzpothaj','zzzaccsharr' etc. Other problems include , dates coming like 11-21-2007. From what you mentioned earlier, You'd see just 'zz' or 'zzz', and dates coming in mmddyy format.

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-11-13 : 12:44:52

Hey Maachie,

What you got in here is a bit complicated problem & Im sure you know that. I ran into a similar situation few months ago & that is what I did. Here is the explanation of what Im doing here.

The UDF pulls out only the numeric components out of the string that is stored in the column, CHDEFAULTLOGIN and then what I do after that is convert that into a DATE column.
There is one more step involved in here & I format that date into a six digit number & then assign a Day value for the first 2 numbers, a Month value for the next 2 numbers and finally an Year value for the last two.

Im not sure what is your confusion about ? Please let me know what you need or if you are not clear about anything that I posted.

regards,
Anil Kumar.

--------------------------------------------------------------------
--Run it from here -------------------------------------------------
--------------------------------------------------------------------

--Function that searches a string and seperates numbers out of it
Create function [dbo].[SeperateNumsInString](@InTheString varchar(5000))
returns varchar(5000)
as
begin
while patindex('%[^0-9]%',@InTheString)>0
Set @InTheString = rtrim(ltrim(replace(@InTheString,substring(@InTheString,patindex('%[^0-9]%',@InTheString),1),'')))
return @InTheString
end

/*
The sql for it using your own test data except for the last row as you havent provided any data for less than 30 day date.
*/

declare @Login table (CHDEFAULTLOGIN varchar(100))
insert into @Login
select 'zzz100107RILEYM4' union
select 'zzz100107Russol' union
select 'zzz100107siekraus' union
select 'zzz100107wangw2' union
select 'zzz100108cassam1' union
select 'zzz100108gascok' union
select 'zzz100108INTCOLMJ' union
select 'zzz100108malirr' union
select 'zzz102108malirr'

select CHDEFAULTLOGIN,
Numbers = dbo.SeperateNumsInString(CHDEFAULTLOGIN),
Date = convert(datetime,'20'+right(substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),2)+left(right('00'+substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),6),4)),
Newdate =
DateAdd(day, -30, convert(datetime,'20'+right(substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),2)+left(right('00'+substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),6),4)))
from @Login
where convert(datetime,'20'+right(substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),2)+left(right('00'+substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),6),4))
> dateadd(day,-30,getdate())

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 13:11:03

Hi sakets_2000

But why your query pulls less rows about 200 something and mine about 6000 something?

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-13 : 13:12:58

Hi Kumar_Anil

CHDEFAULTLOGIN Numbers Date Newdate
zz01042008CHENZ3 010420083 2020-01-04 00:00:00.000 2019-12-05 00:00:00.000
zz01102008honors 01102008 2020-01-10 00:00:00.000 2019-12-11 00:00:00.000
zz01312008COLAKK 01312008 2020-01-31 00:00:00.000 2020-01-01 00:00:00.000
zz02072008INTSHEWA 02072008 2020-02-07 00:00:00.000 2020-01-08 00:00:00.000

I don't get this? Why is the new date col showng 2020 for year? How does this going to get anything prior to 3days old data from the column?

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-11-13 : 13:22:16

Maachie,

Looks like the sample test data that you gave before is different than what you wrote in here now.

As I just explained, Im trying to assign the last 2 digits as Year & Im appending 20 in front of it & that is what is the following piece of code ...

"convert(datetime,'20'+right(substring(dbo.SeperateNumsInString(CHDEFAULTLOGIN),1,6),2)+"

But it looks like you now have different data & what my function is doing is taking the last 2 digits & then adding 20 infront of it & that is how you see 2020.

You can just work on "select CHDEFAULTLOGIN,
Numbers = dbo.SeperateNumsInString(CHDEFAULTLOGIN)," and that will give you only numbers that you need to pay attention to & you can then split them accordingly.

regards,
Anil Kumar.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 13:51:20

quote:
Originally posted by Maachie

Hi sakets_2000

But why your query pulls less rows about 200 something and mine about 6000 something?

The second query I gave you is to see which records don't fit the format you mentioned earlier. Your requirements and the format of data has changed betwteen the posts. If you can outline your problem clearly, It'll be great :)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources