?
Solved

Replacement for GetDate()

Posted on 2004-03-23
7
Medium Priority
?
1,127 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 375 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

649 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