DateTime in "where" clause - 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 2000 Forums

Transact-SQL (2000)

DateTime in "where" clause

Author

Topic

tosto
Starting Member

6 Posts

Posted - 2006-09-06 : 01:37:29

Hi,

i am trying to get 2 columns from my table based on the Date:


SELECT MIM.AMOUNTSTOCKMVTYPEMOTHERSTR
FROM int_movementtype_item_motherstore MIM
WHERE PKDATEMVTYPEMOTHERSTR = '04/09/2006 01:00:00 p.m.'
AND PKITEMMVTYPEMOTHERSTR = 10

I already have data with that paremeters but nothing happend...
0 rows affected. I also tryed to cast the paremeter but nothing.

Does anybody have any idea what it is going on....

Thanks a lot....

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-06 : 01:41:03

Try specifying the date as:
YYYYMMDD:

SELECT MIM.AMOUNTSTOCKMVTYPEMOTHERSTR
FROM int_movementtype_item_motherstore MIM
WHERE PKDATEMVTYPEMOTHERSTR = '20060904 13:00:00'
AND PKITEMMVTYPEMOTHERSTR = 10

tosto
Starting Member

6 Posts

Posted - 2006-09-06 : 01:48:37

I tryed that but it does not work......any idea please

Thanks again

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 01:49:24

post the data in your table, which you are trying to retrive from this query

Chirag

tosto
Starting Member

6 Posts

Posted - 2006-09-06 : 02:20:43

PKMOVEMENTTYPE PKITEM PKDATE AMOUNTSTOCK
1 10 04/09/2006 01:00:00 p.m. 56

this is the data that i have....... the thing is that i need the time and not only the date cause there can be the same item in the same day but in different time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 02:26:40

Is PKDATE stored as DATETIME or SMALLDATETIME? Or are they stored as VARCHAR? A normal date does not have a dot after AM/PM.
Is PKITEM stored as integer? Or are they stored as VARCHAR?

SELECT AMOUNTSTOCK
FROM int_movementtype_item_motherstore
WHERE PKDATE = '04/09/2006 01:00:00 p.m.'
AND PKITEM = '10' --<---- If PKITEM is varchar, otherwise drop ' and '

Peter Larsson
Helsingborg, Sweden

tosto
Starting Member

6 Posts

Posted - 2006-09-06 : 02:30:42

PKITEM is integer and PKDATE is a DateTime, I am using MySQL and it stores the date value like this 04/09/2006 01:00:00 p.m.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 02:42:52

SELECT AMOUNTSTOCK
FROM int_movementtype_item_motherstore
WHERE PKDATE >= '04/09/2006' AND PKDATE < '05/09/2006'
AND PKITEM = 10

Peter Larsson
Helsingborg, Sweden

tosto
Starting Member

6 Posts

Posted - 2006-09-06 : 03:10:29

I also tryed this:

UPDATE `int_movementtype_item_motherstore`
SET AMOUNTSTOCKMVTYPEMOTHERSTR = 10
WHERE PKITEMMVTYPEMOTHERSTR = 10
AND PKDATEMVTYPEMOTHERSTR
in (SELECT MAX(IMM.`PKDATEMVTYPEMOTHERSTR`)
FROM `int_movementtype_item_motherstore` IMM)
AND ASTATEMVTYPEMOTHERSTR = TRUE

cause what I really want is to update the amount of stock depending on the item and the last date/time that the item was inserted.

But I've got this error:

You can't specify target table 'int_movementtype_item_motherstore' for update in FROM clause

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-06 : 03:18:26

[code]
UPDATE i SET AMOUNTSTOCKMVTYPEMOTHERSTR = 10 From int_movementtype_item_motherstore i
WHERE PKITEMMVTYPEMOTHERSTR = 10
AND PKDATEMVTYPEMOTHERSTR = (SELECT MAX(PKDATEMVTYPEMOTHERSTR)
FROM int_movementtype_item_motherstore IMM)
AND ASTATEMVTYPEMOTHERSTR = TRUE
[/code]

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-06 : 09:16:19

http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-06 : 09:19:22

quote:
Originally posted by tosto

PKITEM is integer and PKDATE is a DateTime, I am using MySQL and it stores the date value like this 04/09/2006 01:00:00 p.m.

- Jeff

tosto
Starting Member

6 Posts

Posted - 2006-09-07 : 00:40:26

UPDATE i SET AMOUNTSTOCKMVTYPEMOTHERSTR = 10 From int_movementtype_item_motherstore i
WHERE PKITEMMVTYPEMOTHERSTR = 10
AND PKDATEMVTYPEMOTHERSTR = (SELECT MAX(PKDATEMVTYPEMOTHERSTR)
FROM int_movementtype_item_motherstore IMM)
AND ASTATEMVTYPEMOTHERSTR = TRUE

that from clause does not work in an Update statement

thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 02:37:05

oh.. this is sql server forum, try posting on
www.dbforums.com
www.mysql.com

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources