MS SQL getDate() Function remove Time

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

Re: MS SQL getDate() Function remove Time

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.

I even more strongly suggest not using the format dd-mm-yyyy if you do
store a date as a string. YYYYMMDD is much more versatile. For one
thing it will always convert to the datetime type correctly regardless
of country, and for another it will sort in calendar order.

Roy Harvey
Beacon Falls, CT

On Wed, 04 Jul 2007 07:53:34 -0700, Yas wrote:

>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

Re: MS SQL getDate() Function remove Time

On Jul 4, 3:53 pm, Yas wrote:
> 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.

Re: MS SQL getDate() Function remove Time

On 4 Jul, 17:14, Roy Harvey wrote:
> 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

Re: MS SQL getDate() Function remove Time

On Wed, 04 Jul 2007 08:55:49 -0700, Yas wrote:

>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