Date/TIme Update and Insert Using getdate() ??and Midnight 3

[Coder7]

(1) I must insert a value = tomorrow @ midnight for 'effective date' (which is effective tomorrow, datatype = datetime) into a new table row.

(2) I must also update 'end date' (which is effective tomorrow, datatype = datetime) value = tomorrow @ midnight in an existing row.

For the update, I tried dateadd(dd,1,getdate()) and got the correct date but I don't know how to change the time portion to 0:00:00.

Thank you for any assistance.

 

[tek2002]

i am sure that there is a better way but this is what i would do:
1-store the date as char data type
2-add '00:00:00.000' to the value
3-convert the char data type back to datetime

you can try this out in query analyzer:

declare @date as varchar(12)
select @date = getdate()
select convert(datetime,@date + '00:00:00.000')
select @date

hope it helps.

 

[zhavic]

Also try this

But this example depends on SET DATEFORMAT ( I use USA date format = 101 )
SELECT DATEADD( dd, 1, SELECT DATEADD( dd, 1, CONVERT( char(10), getdate(), 101 ) ) )


This example doesn't care of dateformat
SELECT DATEADD( dd, 1, CONVERT( datetime, CONVERT( char(10), getdate(), 101 ), 101 ) )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[clarkin]

I've found the most consistent way of getting rid of the TIME portion is convert to a FLOAT numeric, round it down with FLOOR() and then convert back to date.

See here for more info: thread183-520948
So to set a date to midnight of the same day:

CONVERT(datetime,FLOOR(CONVERT(float,myDateColumn)))

You can easily use that to get today or tomorrow's date only datetime:

CONVERT(datetime,FLOOR(CONVERT(float,DATEADD(dd,1,getdate()))))

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[Coder7]

Thanks all - good solutions everyone - stars all around.

This is exactly what I needed.

Have a fantastic week.