Are you using SqlServer?
Take a look at the style parameter of CONVERT() here: http://msdn.microsoft.com/en-us/library/ms187928.aspx - in particular have a look at the ODBC canonical (with milliseconds) format.
For short you should use style 121 in the CONVERT() command.
Answer from Widor on Stack OverflowStyle: 121
ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
yyyy-mm-dd hh:mi:ss.mmm(24h)
Are you using SqlServer?
Take a look at the style parameter of CONVERT() here: http://msdn.microsoft.com/en-us/library/ms187928.aspx - in particular have a look at the ODBC canonical (with milliseconds) format.
For short you should use style 121 in the CONVERT() command.
Style: 121
ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
yyyy-mm-dd hh:mi:ss.mmm(24h)
Try this one !
select CONVERT(VARCHAR(10), GETDATE(), 112)
+ right('0'+cast(datepart(hh, GETDATE()) as varchar(2)),2)
+ right('0'+cast(datepart(mi, GETDATE()) as varchar(2)),2)
+ right('0'+cast(datepart(ss, GETDATE()) as varchar(2)),2) as 'DateTime_STR2'
Hi @IH
In SSRS, the now function can return a date value containing the current date and time according to the system. If you want to keep the locale setting, it's as simple as setting the Language expression to =User!Language in the report properties pane.
But using an expression in the report builder to keep the locale setting seems to be difficult to do to display the milliseconds of the date. SSRS does not display the milliseconds of the time by default. If you want to display it, using =FORMAT(NOW(), "MM/dd/yyyy hh:mm:ss.fff tt") seems to be a convenient method, but the time format in the local area will be destroyed.
What I can do is to separate the date from the time, the date is in the regional format, and the time is judged according to the value returned by the now function to determine whether there is "AM", "PM" in it, to choose different formats to display milliseconds.
=FormatDateTime(Now(), DateFormat.ShortDate)&" "&IIF(TimeOfDay().tostring.Contains("PM"),FORMAT(NOW(), "h:mm:ss.fff tt"),
iif(TimeOfDay().tostring.Contains("AM"),FORMAT(NOW(), "h:mm:ss.fff tt"),
FORMAT(NOW(), "HH:mm:ss.fff")))
Preview:
Hope this can help you.
Best regards,
Aniya
None of the suggestions would work. In the end, my solution was to just search the string and replace AM or PM with ".fff 'AM'" or ".fff 'PM'". This seems to work, but not ideal.
Videos
Datetime is not precise to the level of 1 millisecond. What you are asking for is not possible unless you change to a different datatype (i.e. datetime2).
Documentation
Important quote:
Accuracy Rounded to increments of .000, .003, or .007 seconds
@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.
In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).
Select to show values:
;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column
Update joins back to original table:
;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2))
FROM CTE c
JOIN Table1 t ON c.my_id = t.my_id
The following query will get the current datetime and convert into string. with the following format yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar(25), getdate(), 120)
- SQLFiddle Demo
- SQL Server Date Formats
There are many different ways to convert a datetime to a string. Here is one way:
SELECT convert(varchar(25), getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
See Demo
Here is a website that has a list of all of the conversions:
How to Format datetime & date in SQL Server
You can use the handy DATETIMEFROMPARTS:
DECLARE @s nvarchar(16) = '2022020804180454';
SELECT DATETIMEFROMPARTS(LEFT(@s,4), --year
SUBSTRING(@s,5,2), --month
SUBSTRING(@s,7,2), --day
SUBSTRING(@s,9,2), --hour
SUBSTRING(@s,11,2), --minute
SUBSTRING(@s,13,2), --second
SUBSTRING(@s,15,2)*10 --millisecond
)
Inject the needed characters, with STUFF, and then convert:
SELECT CONVERT(datetime2(2),STUFF(STUFF(STUFF(STUFF(V.YourDateString,15,0,'.'),13,0,':'),11,0,':'),9,0,' '))
FROM (VALUES('2022020804180454'))V(YourDateString);
For the people still looking for this, you can use the DATEDIFF_BIG function. This is supported in SQL 2016+, Azure
https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-big-transact-sql
With this query can you get the DateTime to Milliseconds Since 1970
SELECT CAST(Datediff(s, '1970-01-01', GETUTCDATE()) AS BIGINT)*1000
You can use style 121 but you can have only 3 digits for milliseconds (i.e yyyy-mm-dd hh:mi:ss.mmm(24h)) format.
declare @abc varchar(100)='2011-09-26 16:36:57.810'
select convert(datetime,@abc,121)
So you can sort it out by limiting the varchar field to 23 characters before converting as:
declare @abc varchar(100)='2011-09-26 16:36:57.810000'
select convert(datetime,convert(varchar(23),@abc),121)
Or use the Left() function to get first 23 characters as:
select convert(datetime,left(@abc,23),121)
Try to avoid storing date as string.
In case you need 6 digits precision use DATETIME2
SELECT CONVERT(DATETIME2, '2016-08-09T08:08:50.358000', 126) as MSSQLDateTime2
SELECT CONVERT(DATETIME, '2016-08-09T08:08:50.358', 126) as MSSQLDateTime