September 9, 2016 at 8:13 am
Hello All,
I have a datetime field that stores the data in this format 2016-09-09 00:00:00.000. I need to store it in this format 2016-09-09 00:00. I used the datepart function to remove seconds and miliseconds>
SELECT RIGHT('00' + rtrim(cast(DATEPART(year, actiondate) as varchar)), 4) + '-' +
RIGHT('00' + rtrim(cast(DATEPART(month, actiondate) as varchar)), 2) + '-' +
RIGHT('00' + rtrim(cast(DATEPART(day, actiondate) as varchar)), 2) + ' ' +
RIGHT('00' + rtrim(cast(DATEPART(hh, actiondate) as varchar)), 2) + ':' +
RIGHT('00' + rtrim(cast(DATEPART(mi, actiondate) as varchar)), 2)
from table
and I get the results that I need.
However, when I change this query into the update statement the format stays the same.
update table
set actiondate = RIGHT('00' + rtrim(cast(DATEPART(year, actiondate) as varchar)), 4) + '-' +
RIGHT('00' + rtrim(cast(DATEPART(month, actiondate) as varchar)), 2) + '-' +
RIGHT('00' + rtrim(cast(DATEPART(day, actiondate) as varchar)), 2) + ' ' +
RIGHT('00' + rtrim(cast(DATEPART(hh, actiondate) as varchar)), 2) + ':' +
RIGHT('00' + rtrim(cast(DATEPART(mi, actiondate) as varchar)), 2)
What am I doing wrong?
September 9, 2016 at 8:27 am
Is datetime the actual data type of the column? If so, then (quite rightly so) you cannot change the way the value is stored. It's stored as a date and time. If you want to change the way the data is displayed, then by all means use something in your SELECT statement like what you tried (although it would be easier to convert to varchar(n), where n is the number of characters you want in your date string). Better still, have your presentation layer do that for you.
John
Edit - changed the "although it would be easier" part to simplify it
September 9, 2016 at 8:32 am
Yes datetime is the actual datatype of the column.
September 9, 2016 at 8:54 am
You cannot change how things are stored. You change how they are displayed.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 9, 2016 at 2:55 pm
If you don't care about hours, minutes and seconds, your column should have a datatype of DATE, rather than DATETIME.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 14, 2016 at 2:37 pm
SMALLDATETIME gets you closer to what you want.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply