Operations With Dates*

Question

Post reply

Operations With Dates*

er sql

SSC Eights!

Points: 943
More actions
June 6, 2005 at 10:08 am

#89281

Hi, I need some help with:
I need to compare a varchar column that stores a date value with getdate value, if the difference between them is a month I have to update a status value for that column.
Does anyone has an Idea how to do that?.
I've created a script to get year, month and day.
DECLARE @ACTUAL
SELECT SUBSTRING(REQUESTED_DATE, 1, 4) AS YEAR,
SUBSTRING(REQUESTED_DATE, 5, 2) AS MONTH,
SUBSTRING(REQUESTED_DATE, 7, 2) AS DAY
FROM REQUESTE
WHERE ID_REQUEST = 1
SELECT @ACTUAL GETDATE()
How Can I Compare and Convert Getdate value to make the operation?
Thanks everyone.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Hi there,

Something like this might work.

CREATE TABLE TestTable (DateColumn VARCHAR(255),

StatusColumn INT)

GO

INSERT INTO TestTable (DateColumn) SELECT '6 JUNE 2005 11:58'

INSERT INTO TestTable (DateColumn) SELECT '1 JANUARY 2005 11:58'

GO

SELECT DATEDIFF(MONTH, CAST(DateColumn AS DATETIME), GetDate()),

DateColumn

FROM TestTable

GO

UPDATE TestTable SET StatusColumn = 1 WHERE DATEDIFF(MONTH, CAST(DateColumn AS DATETIME), GetDate()) > 1

GO

SELECT * FROM TestTable

GO

Good luck

Wayne

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

DECLARE @MyDate DATETIME

SET @MyDate = '05/07/2005'

IF DATEDIFF(MONTH, @MyDate, GETDATE()) = 1

SELECT @MyDate MyDate, GETDATE() Todaye, 'PREVIOUS MONTH'

IF CONVERT(VARCHAR, DATEADD(MONTH, 1, @MyDate), 101) = CONVERT(VARCHAR, GETDATE(), 101)

SELECT @MyDate MyDate, GETDATE() Todaye, 'EXACTLY ONE MONTH DIFFERENCE'

IF CONVERT(DATETIME,CONVERT(VARCHAR, DATEADD(MONTH, 1, @MyDate), 101)) > CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(), 101))

SELECT @MyDate MyDate, GETDATE() Todaye, 'LESS THAN ONE MONTH DIFFERENCE'

IF CONVERT(DATETIME,CONVERT(VARCHAR, DATEADD(MONTH, 1, @MyDate), 101)) < CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(), 101))

SELECT @MyDate MyDate, GETDATE() Todaye, 'MORE THAN ONE MONTH DIFFERENCE'

Regards,
gova

er sql SSC Eights! Points: 943 More actions · Answer 3

er sql

SSC Eights!

Points: 943

June 6, 2005 at 10:43 am

#563282

Excelent It's working.

Thanks guys.

er sql SSC Eights! Points: 943 More actions · Answer 4

Finally,I need to update status column when my Request_Date value is the same that getdate value, the time value is not the same, in my column I have 2005-06-06 00:00:00.000 and getdate has 2005-06-06 12:40:22.273.

The date is the same but not the time value, how can I compare and update my status column value?

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Declare @StartD as datetime

Declare @EndD as datetime

Set @StartD = Dateadd(d, datediff(d, 0, GetDate()), 0)

set @EndD = dateadd(d, 1, @StartD)

Select @StartD, @EndD

Select * from dbo.YourTable where DateCol >= @StartD and DateCol < @EndD --(do no use between... might get too many records)

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 6

Expanded version of the earlier example:

CREATE TABLE TestTable (DateColumn VARCHAR(255),

StatusColumn INT)

GO

INSERT INTO TestTable (DateColumn) SELECT '6 JUNE 2005 11:58'

INSERT INTO TestTable (DateColumn) SELECT '5 JUNE 2005 11:58'

INSERT INTO TestTable (DateColumn) SELECT '1 JANUARY 2005 11:58'

GO

-- test SELECT DATEDIFF(MONTH, CAST(DateColumn AS DATETIME), GetDate()) as months,

-- test DATEDIFF(DAY, CAST(DateColumn AS DATETIME), GetDate()) as days,

-- test DATEPART(DAY, CAST(DateColumn AS DATETIME)),

-- test DateColumn

-- test FROM TestTable

-- test GO

UPDATE TestTable SET StatusColumn = 1 WHERE DATEDIFF(MONTH, CAST(DateColumn AS DATETIME), GetDate()) > 1

GO

UPDATE TestTable

SET StatusColumn = 2

WHERE DATEDIFF(DAY, CAST(DateColumn AS DATETIME), GetDate()) = 0

AND DATEPART(DAY, CAST(DateColumn AS DATETIME)) = DATEPART(DAY, GetDate())

GO

SELECT * FROM TestTable

GO

hope this helps

Wayne

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

The server will not be able to use an index seek in you use datediff() and datepart(). You must use something like ">=" operators if you want to have a change at an index seek. Might not make a big difference if the table is very small, but it all adds up in the end.