| Author |
 Topic  |
|
|
bagzli
Starting Member
Canada
17 Posts |
 Posted - 04/23/2012 : 14:59:35
|
Hello,
I am trying to check for records in the past 30 days that have a timestamp in the following order yyyymmddhhmmss starting from today`s date.
I have no idea how to go about creating the timestamp for today`s date, and yes it really has to be in that format.
Help is very appreciated,
Regards, |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 04/23/2012 : 15:07:47
|
whats the datatype of your current timestamp column? ideally you should be having it as datetime in which case solution would be as simple as
select * from yourtable where yourtimestampcolumn >= dateadd(dd,datediff(dd,0,getdate())-29,0)
and yourtimestampcolumn< dateadd(dd,datediff(dd,0,getdate())+1,0)
in current case you need to do a convert for making it a datetime value and do date comparison
select * from yourtable where convert(datetime,stuff(stuff(stuff(stuff(stuff(yourtimestampcolumn,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':'),120) >= dateadd(dd,datediff(dd,0,getdate())-29,0)
and convert(datetime,stuff(stuff(stuff(stuff(stuff(yourtimestampcolumn,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':'),120) < dateadd(dd,datediff(dd,0,getdate())+1,0)
thats why its always recommended to use proper datatype for fields
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
bagzli
Starting Member
Canada
17 Posts |
Posted - 04/23/2012 : 15:12:44
|
| its actually stored as a char in yyyymmddhhmmss format so example is 20120521000000 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 04/23/2012 : 15:17:45
|
hmm...you're making date manipulations diffcult by storing like this. By introducing unnecessary converts, you're making query engine do costly operations. Also it will cause the query optimiser to ignore an index if already present in field owing to use of functions over it making it non SARGable
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bagzli
Starting Member
Canada
17 Posts |
Posted - 04/23/2012 : 15:19:57
|
It wasn't my choice how any of this is stored, and this is what I have to work with, is there any hope to get the results out? Maybe convert the chars to timestamp and then compare or vice-verse?
or maybe make it a two step process, first convert the char field into datetime and then do a compare?
Ugh i'm grasping and strings here... |
Edited by - bagzli on 04/23/2012 15:25:09 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 04/23/2012 : 15:25:03
|
thats what the second suggestion does. Did you try it yet?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bagzli
Starting Member
Canada
17 Posts |
Posted - 04/23/2012 : 15:26:19
|
| I don't quite understand what the second example does, i tried the first one and it doesn't work. What do I need to substitute for the numbers that you have in the second example? also where it says "stuff" not sure what is suppose to go there |
Edited by - bagzli on 04/23/2012 15:27:00 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 04/23/2012 : 15:44:23
|
This should make use of an index, if present:SELECT *
FROM <Table_Name>
WHERE <Column_Name> > REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), DATEADD(DAY, -30, CURRENT_TIMESTAMP), 121), '-', ''), ' ', ''), ':', '') |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 04/23/2012 : 15:45:34
|
quote:
Originally posted by bagzli
I don't quite understand what the second example does, i tried the first one and it doesn't work. What do I need to substitute for the numbers that you have in the second example? also where it says "stuff" not sure what is suppose to go there
you just need to replace yourtable part with your table name and put correct column name containing timestamp values inside stuff expression
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bagzli
Starting Member
Canada
17 Posts |
Posted - 04/23/2012 : 16:14:06
|
Example: the column name for stamp is mystamp, table name is mytable, database name is mydb
select * from mytable where convert(datetime,stuff(stuff(stuff(stuff(stuff(mystamp,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':'),120) >= dateadd(dd,datediff(dd,0,getdate())-29,0)
and convert(datetime,stuff(stuff(stuff(stuff(stuff(mystamp,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':'),120) < dateadd(dd,datediff(dd,0,getdate())+1,0)
I'm just confused what is suppose to be instead of stuff? or stuff is suppose to be there... |
Edited by - bagzli on 04/23/2012 16:25:20 |
|
|
|
bagzli
Starting Member
Canada
17 Posts |
Posted - 04/23/2012 : 16:18:42
|
quote:
Originally posted by Lamprey
This should make use of an index, if present:SELECT *
FROM <Table_Name>
WHERE <Column_Name> > REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), DATEADD(DAY, -30, CURRENT_TIMESTAMP), 121), '-', ''), ' ', ''), ':', '')
I get an error:
"there was an error in this WHERE expression, field DAY was not found in the VIEW"
I don't know if this matters but I'm using a software called SEQUEL. I'm wondering if software is the issue |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 04/23/2012 : 16:23:11
|
Whatever We've given you are T-sql queries which will work fine in standard SSMS query editor. I dont know what SEQUEL is. Is it a client tool? Whats your RDBMS by the way? is it really sql server?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
bagzli
Starting Member
Canada
17 Posts |
Posted - 04/23/2012 : 16:30:56
|
Well, i'm a co-op student here and I've created queries before and I only know SQL. I did run into problems with join statements, specifically inner and out joins. But I did get it done using Join's so i'm assuming it is SQL. No idea what my RDBMS is.
I did have an idea though, can I somehow turn today's date into a matching char value and then just compare them. Because when I do a compare with a specific matching char value, it works.
Example:
Select * from mytable where mystamp < 201205120000 |
|
|
|
bagzli
Starting Member
Canada
17 Posts |
Posted - 04/24/2012 : 09:30:05
|
Select * from myTable where mystamp < &today_date and mystamp > (&today_date - 30)
mystamp = char field length of 12, &today_date = char field length of 12 but being converted from today's date into a stamp into a string/char.
that is basically what I got and i'm still dead on stuck trying to figure out how to convert today's date into this variable and then subtract 30 days for second part of the equation
Select * from myTable where mystamp < TIMESTAMP(CURRENT DATE [,CURRENT TIME]) and mystamp > (&today_date - 30)
Can something like this work? |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 04/24/2012 : 10:48:34
|
| This site is specific to Microsoft SQL Server. If you are using a different RDBMS, then we probably can't help you. My suggestion, would be to find a forum site specific to your flavor of SQL. |
|
|
|
bagzli
Starting Member
Canada
17 Posts |
Posted - 04/24/2012 : 12:30:48
|
well i got a solution, should anyone encounter into a similar problem here is what you may try:
SELECT *
FROM myTable
WHERE CVTDATE(SST(myField, 1, 8), YMD1)>= CURRENT DATE-30 DAYS
thanks for all the help, btw if this is not your type of RDBMS i have no idea what it is then, so feel free to correct my knowledge so in future i can go to appropriate forum. |
Edited by - bagzli on 04/24/2012 12:33:27 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48041 Posts |
Posted - 04/24/2012 : 21:24:01
|
quote:
Originally posted by bagzli
well i got a solution, should anyone encounter into a similar problem here is what you may try:
SELECT *
FROM myTable
WHERE CVTDATE(SST(myField, 1, 8), YMD1)>= CURRENT DATE-30 DAYS
thanks for all the help, btw if this is not your type of RDBMS i have no idea what it is then, so feel free to correct my knowledge so in future i can go to appropriate forum.
this is definitely not t-sql which we deal in this forum
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
timestamp today`s date
