MS SQL getDate() Function remove Time

[Yas]

Hi,

I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.

The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.

In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.
Any help will be appricated.

Thank you!

Yas

[Stephen2]

On Jul 4, 3:53 pm, Yas <[e-mail]:

Hi,

I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.

The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.

In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.

Any help will be appricated.

Thank you!

Yas

AFAIK a there is no DATE type in MS SQL, only DATETIME so you cannot
store only the date part.
You can use SELECT CONVERT(VARCHAR(8),datevalue,112) to return the
datetime in YYYYMMDD format without the time but it's stored as a
VARCHAR not a DATETIME.

[Yas]

On 4 Jul, 17:14, Roy Harvey <[e-mail]:

To remove the time from a datetime such as getdate():

SELECT dateadd(day,datediff(day,0,getdate()),0)

I strongly suggest not storing a date column as a string. Use a
datetime and just set the time to zeroes if you only need the date.

Hi I am storing the coumn as datetime and not string. However, using
the above suggestion (day,datediff(day,0,getdate()),0) I get a column
with Date+Time set to Zeros. OK, but the problem is when I run the
update/insert records command again It doesn't overwrite the columns
with today's date, its as if sql is secretly inserting the time by it
self and even though to my eyes the rows is exactly the same SQL adds
a new row thinking it is distinct.

I would like that if the table had a rowOld with: (ColValue1,
ColValue2,2007-07-04 00.00.00.000)
If I use the above suggestion and insert a rowNew with same values
(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
....It should overwrite rowOld with rowNew, not insert rowNew as a new
row.
....and only insert as a new rowNew2 when this row has a different date
eg. 2007-07-05 00.00.00.000

I thought it would as time is now set to Zeros, but it doesn't. Is SQL
marking each row in the examples above with a time stamp? even though
it is not shown in the row value?
Thanks again :-)

Yas

[Roy Harvey]

On Wed, 04 Jul 2007 08:55:49 -0700, Yas <ya****@gmail.comwrote:

>I would like that if the table had a rowOld with: (ColValue1,
ColValue2,2007-07-04 00.00.00.000)
If I use the above suggestion and insert a rowNew with same values
(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
...It should overwrite rowOld with rowNew, not insert rowNew as a new
row.
...and only insert as a new rowNew2 when this row has a different date
eg. 2007-07-05 00.00.00.000

You can write an INSERT for a new row, or an UPDATE for an existing
row, but you have to choose which it is to be. In your case you have
to find out if the row exists and then run INSERT or UPDATE depending
on what you find.

Microsoft is adding MERGE to the next release of SQL Server, which
would allow you to write one command to accomplish both functions, but
it is not available today.

Roy Harvey
Beacon Falls, CT