October 10, 2012 at 8:15 am
Hi,
I ran an update on one record to update the datetime using current_timestamp
I might be losing it...
but before the update when I looked at a date/time field its looked like 2012-10-10 10:13:37
After the update it looks like 2012-10-10 10:13:37.423
Is it me or did the update change the display?
Can I change it back to 2012-10-10 10:13:37
Thanks
Joe
October 10, 2012 at 8:20 am
jbalbo (10/10/2012)
Hi,I ran an update on one record to update the datetime using current_timestamp
I might be losing it...
but before the update when I looked at a date/time field its looked like 2012-10-10 10:13:37
After the update it looks like 2012-10-10 10:13:37.423
Is it me or did the update change the display?
Can I change it back to 2012-10-10 10:13:37
Thanks
Joe
The update certainly did not change your display. In your "pre-update" you don't have the milliseconds. The Datetime datatype will always have the milliseconds. They could be .000 but they are always present. You can set the milliseconds to 000 if you want but I think there is just some confusion on what you are seeing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2012 at 8:25 am
Is the "date/time field" you are updating actually a DATETIME data type?
What is the actual data type?
October 10, 2012 at 8:30 am
yes definitly datetime, double checked to make sure I haven't lost it.... lol
October 10, 2012 at 9:21 am
jbalbo (10/10/2012)
yes definitly datetime, double checked to make sure I haven't lost it.... lol
Just wondering, because different types of date/time data types have different display formats in SSMS.
declare @date date,
@datetime datetime,
@datetime2_0 datetime2(0),
@datetime2_7 datetime2(7)
select@date= getdate() ,
@datetime= getdate(),
@datetime2_0= sysdatetime(),
@datetime2_7= sysdatetime()
select[@date]= @date ,
[@datetime]= @datetime,
[@datetime2_0]= @datetime2_0,
[@datetime2_7]= @datetime2_7
Results:
@date @datetime @datetime2_0 @datetime2_7
---------- ----------------------- ---------------------- ----------------------
2012-10-10 2012-10-10 11:16:59.737 2012-10-10 11:17:00 2012-10-10 11:16:59.73
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply