GETDATE -> only DATE needed
-
WHen you execute eg a sql statement
"UPDATE tbldummy SET datefield = GETDATE()"
You get a timestamp, How is it possible to only add the datepart and drop the time?
-
UPDATE tbldummy SET datefield = convert(char(10), getdate(),121)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
There are numerous ways to this.
You basically have to convert it to a string first. convert(varchar,getdate(),101) will get you there. (this way I don't have to set the char(xx) attribute, since varchar will default to 30 anyway).
You then usually will convert it back to a datetime datatype so you'll end up with: convert(datetime, convert(varchar, getdate(), 101))
Another variation of this is: convert(datetime, datediff(day, 0, getdate())) (this is supposedly faster)
You can then encapsulate this into a user-defined function so that you could just call SELECT dbo.f_DateOnly(getdate()).
David -
Not to be a nitpicker, but the above actually stores the value as a datetime with the time = 00:00:00AM (i.e. Jan 23 2004 12:00AM). This makes date math work since the time portion is the same for all rows, but you still need to be careful in doing date comparisons (i.e. date1 <= date2) unless you format all your dates, including those in query or view critieria, using this method. Elsewise you end up comparing dates in which one has a time of 00:00 and one has an actual time. Comparisons, especially "=" won't work there. Example: "SELECT * from Table1 where Date1 = Getdate()" will never work since Date1 has a time portion of 00:00, while Getdate() has a time portion of the current time. You'd have to use "SELECT * from Table1 where Date1 = convert(datetime, datediff(day, 0, getdate()))" to get a valid comparison.
If I'm storing dates that I will be using only for comparison purposes I reformat them into the format "yyyymmdd" and store it as an integer. This makes comparisons work perfectly. Of course these fields can't be used for date math (i.e. DateAdd) without reconverting to a datetime format.
There's a UDF, FormatDate, I believe on this site somewhere, that will format a date into any format string similar to VB functionality: FormatDate(getdate(),'yyyymmdd') yields the above result. If you can't find it here let me know and I'll post it.
-
Actualy, I don't consider this to be nitpicking
Your comments do point to brainteaser issues, when one does not keep in mind sqlserver is always registering date and time, and when someone else does not take your user-defined-restrictions (timepart always 00:00:00am) it can get everyone in troubles. So there may be a need to check input and update for that column (maybe trigger or other scheduled process).
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
> So there may be a need to check input and update for that column (maybe trigger or other scheduled process).
How about just a CHECK constraint?Also, don't use a UDF to convert dates to YYYYMMDD, just CONVERT(char(8),YourDate,112). The native function (and implicit conversion if the result is going into an int column) will be faster than any UDF, and you'll appreciate this speed if you're actually updating an entire table.
--Jonathan -
Also, don't use a UDF to convert dates to YYYYMMDD, just CONVERT(char(8),YourDate,112). The native function (and implicit conversion if the result is going into an int column) will be faster than any UDF, and you'll appreciate this speed if you're actually updating an entire table.
True. Didn't see the 112 in the list.....
-
Thank you for the discussion above ...
CONVERT(char(8),YourDate,112) seems nice, now to braintease again, is there a function in the other direction that works?
...
-
If the YYYYMMDD value is a string, it will be implicitly converted to a date type. If you instead use it as an integer, first convert the integer to a string, e.g. CAST(CAST(20021023 AS char(8)) AS datetime).
--Jonathan -
Well it can actually depend on yuor version of SQL and some settings I forget. If you store as a string and compare to a string SQL in most cases will compare as string, if comparing to a date SQL may convert to a compatible date format (it is a real value with whole number being days and decimal being time) or the other value may be converted to a compatible string which because yyyymmdd sorts alphabetically properly will not be an issue anyway. If you want to be sure how it is handled use CAST(dateval AS smalldatetime) to control explicitly. My personal opinion is always be explicit because some implicit conversions have changed between versions and code that behaved one way in 7 may not behave the same in 2000.
-
You simply use something like this:
UPDATE tbldummy SET datefield = CONVERT(VARCHAR(8), GETDATE(), 112)
Works great
If you can do a select query, but you want drop the time you can use:
select CONVERT(VARCHAR(10), datefield, 101)
from tbldummy
It will return the date in format:
dd/mm/yyyy
You can see the convert function for more information
-
Convert works both ways with the format.
CONVERT(char(8),YourDate,112) - to convert to CHAR .
CONVERT(YourDate, char(8),112) - to convert it back to (SMALL)DATETIME
Personally I do not like to Convert date data types to character data types in order to remove the time portion ( or make it midnight - as some pointed out here
). I use DATEADD(day, DATEDIFF(day, 0, @date), 0) method to get rid of the time. This is one of the fastest methods. There are some old discussions on this forum about this subject and someone run some tests to compare the different methods.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply