inserting 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

inserting today's date

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-09-11 : 17:23:02

I'm trying to insert today's date into a field in my database. This is what I've been trying and it doesn't work...

UPDATE [order] SET
decisionDate = GetDate()
WHERE orderID = '1001' ;

Do I have to use something other than GetDate() ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 17:28:00

Explain "doesn't work" for us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-09-11 : 17:33:16

query analyzer tells me it works:

(1 row(s) affected)

The decisionDate field stays null in the db though.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 17:36:13

Post the output of these:

SELECT GETDATE()

SELECT orderID, decisionDate
FROM [order]
WHERE orderID = '1001'

What is the data type of orderID?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-09-11 : 17:51:09

ahhhh nice. I had no idea that GetDate() returned time also. I'm trying to return rows where decisionDate = todays date. I thought the problem was in the update I'm doing when an order is decided on, but it's how I'm trying to get all orders with a decision date of today.

I wasn't getting anything back because I'm setting decisionDate = GetDate(). 1 second later, GetDate() isn't going to be the same so I can't look for GetDate() = GetDate().

Is there a way that I can return rows where decisionDate = today's date, if I'm inserting decisionDate as GetDate()?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 17:55:24

DECLARE @d datetime

SET @d = GETDATE()

UPDATE [order]
SET decisionDate = @d
WHERE orderID = '1001'

You'll always get the time with datetime data type, but you can get it to be 00:00 for time by using this: DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-09-11 : 17:57:53

Sorry, that's probably very confusion. What I'm trying to do is this...

A query like this is used to update an order when a decision is made on it:
UPDATE [order] SET
decisionDate = GetDate()
WHERE orderID = '1001' ;

A query like this is used to display all orders who had a decision made on them today:
SELECT *
FROM [Order}
WHERE decisionDate = today's date ( I was using GetDate() here)

Thanks, sorry for being so vague

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 18:00:20

[code]
SELECT *
FROM [Order]
WHERE
decisionDate >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0) AND
decisionDate < DATEADD(Day, DATEDIFF(Day, 0, GetDate()+1), 0)
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-09-11 : 18:09:12

Thank you, works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources