Solved

Replacement for GetDate()

Posted on 2004-03-23
7
1,119 Views
Last Modified: 2012-06-21
We are trying to set up a job in SQL Server 2000 so that at a certain day and time, a column in our database will be updated with the current date but not the time.

The following works:

Update tablename
Set newdate = GetDATE ()

GO

but it dumps in the full date and time of the server. We just need the date and we do NOT want the time.

The date needs to be in the format of mm/dd/yyyy .  Nothing I have tried so far works.  I keep getting all kinds of errors.  Could someone please correct the code I have above to what it should read, to update the column newdate, with the correct date without the time?

Thanks!
0
Comment
Question by:khockady
7 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 10664111
set newdate=convert(char(10),getdate(),101)

Brett
0
 
LVL 34

Expert Comment

by:arbert
ID: 10664114
You realize that a datefield does store date/time right?  Generally you store both the date and time and use CAST/CONVERT to extract the parts you need later.

Brett
0
 
LVL 12

Expert Comment

by:esteban_felipe
ID: 10664155
Hi khockady,

Here's a small script that represent what would I do:
---------------------------------------------------------------------------------
Declare @BaseDate datetime
Declare @DateWithoutTime datetime
Declare @FinalDate datetime
Set @BaseDate = '2001-03-22 20:13:18.077'

Set @DateWithoutTime= Convert(datetime,
            cast(DatePart(yy,getDate()) as varchar(4)) + '-' +
            cast(Datepart(mm,getDate()) as varchar(2)) + '-' +
            cast(DatePart(dd,getDate()) as varchar(2)))

Set @FinalDate =
      DateAdd(hh,DatePart(hh,@BaseDate),
            DateAdd(mi,DatePart(mi,@BaseDate),
                  DateAdd(ss,DatePart(ss,@BaseDate),
                        DateAdd(ms,DatePart(ms,@BaseDate),@DateWithoutTime))))

Print @FinalDate
---------------------------------------------------------------------------------
Basicly it will create a datetime value from getDate() ignoring time, then it will add milliseconds, seconds, minutes and hours taken from the @BaseDate value. Applying this procedure to an update statement it would be:

Update
      tablename
set newDate =
      DateAdd(hh,DatePart(hh,newDate),
            DateAdd(mi,DatePart(mi,newDate),
                  DateAdd(ss,DatePart(ss,newDate),
                        DateAdd(ms,DatePart(ms,newDate),
                              Convert(datetime,
                                          cast(DatePart(yy,getDate()) as varchar(4)) + '-' +
                                          cast(Datepart(mm,getDate()) as varchar(2)) + '-' +
                                          cast(DatePart(dd,getDate()) as varchar(2)))))))

I hope it helps


Esteban Felipe
www.estebanf.com
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:arbert
ID: 10664199
Seems a little overcomplicated--the returned value doesn't look like the requested format (time is still included)....
0
 
LVL 2

Expert Comment

by:MePhantom
ID: 10664481
Hi khockady,

If newdate is defined as datetime
If you set newdate = to just the date (like newdate='2004-03-23') you will still get date AND time when you do a select newdate. In this case time will be 00:00:00, but it will still show.

Use the set statement as you show it, however on the select you use to look at this use

Select convert(char(10),newdate,101)

This will show newdate in the mm/dd/yyyy format. + you will still have time if you ever need it.
And Sooner or later somebody will want that time 8o)

Of course you could also define newdate as char[10] and use arbert's set statement.
Not the best way to store date in my opinion, but its your app.

Cheers!
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10667620
If the field in your database that you're comparing against has a time componant that is other than 00:00:000, then you're right, any comparision, even with a date with a time of 00:00:000 will not return what you want.  So, you have a couple of options.
If there is a lot of data, or you have a great index on your date field, dont use the first method, it alters the format of your date field for the comparision. The second one will get you all the values 'today', regardless of time.

where convert(char(10),yourdatefield,101) = convert(char(10),getdate(),101)  ---converts your date field so an index wouldnt be well used

where yourdatefield between convert(datetime,convert(char(10),getdate(),101)) and getdate()  --so between today 00:00:000 and today current time


0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10667668
Sorry, read it wrong, thought you were looking to execute within a certain date range.
SQL dates are stored as datetime (until Yukon). However there is nothing to force you to use the time componant for display. Use the convert/convert for your assignment (it will store 0 time that way)

convert(datetime,convert(char(10),getdate(),101))

When you display the field, in your select, convert it to character for display using convert(char(10),yourdatefield,101)

Storing a date as a character field will often lead to issues in retrieving and sorting data later, not to mention enforcing integrity. Also, by keeping it as a datetime field, if somewhere down the road, someone in managment decides they like the date to be formatted as dd-mm-yyyy you can easily do it without making any data changes.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question