Problem using date function with UPDATE

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

Problem using date function with UPDATE

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-08-17 : 07:56:41

Hi,
I am trying to update a time field with this query:


UPDATE table
SET datepart(hh,DateColumn) = datepart(dd,DateColumn)

but it does not work. I.e. I would like to change the hour field and that date to be identical to it's date field.

I receive this output:

Incorrect syntaz near ','

Can someone give me some input on this one?

Best regards,
KFluffie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 08:00:30

Please give us some examples. It's very hard to follow your logic.

N 56°04'39.26"
E 12°55'05.63"

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-17 : 08:21:56

Hi

You can’t able to update the part of data in columns. But you can able to retrieve the part of data and also possible to check the part of column data in where clause.



CREATE TABLE DAT(ID INT IDENTITY(1,1), DATE DATETIME)


INSERT INTO DAT
SELECT GETDATE()-1

INSERT INTO DAT
SELECT GETDATE()+1

INSERT INTO DAT
SELECT GETDATE()


SELECT * FROM DAT where 
DATEPART(HH,DATE) = DATEPART(DD,DATE)

SELECT * FROM DAT where 
DATEPART(HH,DATE) = DATEPART(HH,DATE)

DROP TABLE DAT

-------------------------
R...

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-08-17 : 08:24:55

UPDATE <TableName>
SET <DateColumn1> = dateadd(hh,(-1 * datepart(hh, DateColumn1))+datepart(dd,DateColumn2),DateColumn1)

This will only work if date is less than 24 as pointed by Peso
Rahul Shinde

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-08-17 : 08:29:09

quote:
Originally posted by Peso

Please give us some examples. It's very hard to follow your logic.

N 56°04'39.26"
E 12°55'05.63"

Yepp,
In our database we import data based on a date. This date is for which dates the transaction was done (i.e. when an order line was created).

I need to import the same order line, which have been updated in different transactions, and I need to import it on the same date (not transaction date, another date field in one of our table) but at the same time the time field should be in the correct order (i.e. when the first order line appeared should have the earliest time stamp on the same day.)

Example:
Transaction date:
2009-08-10 15:39:02
2009-08-12 10:39:02
2009-08-14 21:39:02

Should be:
2009-08-10 10:39:02
2009-08-12 12:39:02
2009-08-14 14:39:02

I need them to be in on the same day as well as in correct order so that our other jobs calculate the date in correct order.

/KFluffie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 08:32:58

What happens to TransactionDates that occurs on the 24th, 25th to 31st of month?

N 56°04'39.26"
E 12°55'05.63"

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-08-17 : 08:36:49

quote:
Originally posted by Peso

What happens to TransactionDates that occurs on the 24th, 25th to 31st of month?

N 56°04'39.26"
E 12°55'05.63"

This is just an manual handling from my side to have the transaction imported on the same date. This is not how we handle the transaction normally.

Best regards,
KFluffie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 08:38:28

Yes, but you can't set a time to have an hour more than 23.
If the date is August 30, the hour part of time component cannot be set to 30.
What do you want to happen for records with dates between 24 and 31?

N 56°04'39.26"
E 12°55'05.63"

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-08-18 : 04:45:01

quote:
Originally posted by Peso

Yes, but you can't set a time to have an hour more than 23.
If the date is August 30, the hour part of time component cannot be set to 30.
What do you want to happen for records with dates between 24 and 31?

N 56°04'39.26"
E 12°55'05.63"

Hi Peso,
then I will change the minute instead of the hour, which also works.

I know that this an overall bad solution but it is the most effective way on how to solve my issue which just happen a couple of times per year. There is no other way to solve this issue.

Best regards,
KFluffie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 05:23:16

[code]DECLARE @Sample TABLE
(
dt DATETIME
)

INSERT @Sample
SELECT '2009-08-10 15:39:02' UNION ALL
SELECT '2009-08-12 10:39:02' UNION ALL
SELECT '2009-08-14 21:39:02'

SELECT dt,
DATEADD(MINUTE, DATEPART(DAY, dt) - DATEPART(MINUTE, dt), dt)
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources