timestamp today`s 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

General SQL Server Forums

New to SQL Server Programming

timestamp today`s date

Author

Topic

bagzli
Starting Member

17 Posts

Posted - 2012-04-23 : 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

52326 Posts

Posted - 2012-04-23 : 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

17 Posts

Posted - 2012-04-23 : 15:12:44

its actually stored as a char in yyyymmddhhmmss format so example is 20120521000000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 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

17 Posts

Posted - 2012-04-23 : 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...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 15:25:03

thats what the second suggestion does. Did you try it yet?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bagzli
Starting Member

17 Posts

Posted - 2012-04-23 : 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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-23 : 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

52326 Posts

Posted - 2012-04-23 : 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

17 Posts

Posted - 2012-04-23 : 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...

bagzli
Starting Member

17 Posts

Posted - 2012-04-23 : 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

52326 Posts

Posted - 2012-04-23 : 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

17 Posts

Posted - 2012-04-23 : 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

17 Posts

Posted - 2012-04-24 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-24 : 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

17 Posts

Posted - 2012-04-24 : 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources