2

I have a date that is in the unix format (13 digits, not 10):

/Date(1561476867713)/

I want to convert that date within VB so that I can use it as a parameter for a SQL 'select' query:

SELECT TOP 1 *
FROM QB
WHERE QB.BOQ_ITEM_UID = 9950
AND QB.CONTRACT_UID = 0
AND QB.CONTRACTOR_UID = 3
AND QB.QUANTITY_BREAK = 200
AND QB.RATE_START_DATE = [converted date]

what the query should find (note the time is in milliseconds):

SQL

I have found various solutions but none that I could get to work for this particular situation. Using Visual Basic and SQL Server 2016.

EDIT: to demonstrate that the solution I have found to similar problems doesn't work:

Dim timeStamp = "1561476867713"
Dim unixConvertedDate As DateTime = New 
System.DateTime(1970, 1, 1, 0, 0, 0, 0)
unixConvertedDate = unixConvertedDate.AddMilliseconds(timeStamp).AddHours(1)

This returns '#6/25/2019 04:34:27 PM#'. Now I need to change that to: '2019-06-25 16:34:27.713'

The issue is that '#6/25/2019 04:34:27 PM#' isn't displaying the milliseconds, only seconds, so I cannot format it to look like this: '2019-06-25 16:34:27.713'

NickyLarson
  • 139
  • 8
  • SQL server 2018 – NickyLarson Jul 02 '19 at 09:42
  • Did you check [this related question]()?https://stackoverflow.com/q/2904256/5089204 – Shnugo Jul 02 '19 at 09:50
  • @Shnugo I need to do it using VB – NickyLarson Jul 02 '19 at 09:54
  • @NickyLarson, It took just seconds to find the answer above. Just do some research on VB and conversion of UNIX epoche dates. Otherwise it should be easy to understand the few lines in the linked answer and do the same within VB... – Shnugo Jul 02 '19 at 09:56
  • `Dim dotNetDate as Date = `[DateTimeOffset.FromUnixTimeMilliseconds](https://learn.microsoft.com/en-us/dotnet/api/system.datetimeoffset.tounixtimemilliseconds) `(...).DateTime` – Jimi Jul 02 '19 at 10:13
  • I have tried various solutions, been on this since yesterday morning. I wouldn't have asked otherwise. – NickyLarson Jul 02 '19 at 10:18
  • If you have this input: `/Date(1561476867713)/`, extract the *number* part (the milliseconds), convert to `Int64` and use that method to convert to a .Net `DateTime`. – Jimi Jul 02 '19 at 10:22
  • I have. I've updated my post to demonstrate that it is not working. – NickyLarson Jul 02 '19 at 10:32
  • That result is correct. You should mention what you were expecting instead. Maybe an UTC time/offset instead of the Local one? Or the other way around? – Jimi Jul 02 '19 at 10:35
  • Extract the value as: `Dim unixMilliseconds as Long = Convert.ToInt64(Regex.Match("/Date(1561476867713)/", "\((.*?)\)").Groups[1].Value)`. See whether `Dim netLocalDate = DateTimeOffset.FromUnixTimeMilliseconds(unixMilliseconds).LocalDateTime` is what you're looking for. – Jimi Jul 02 '19 at 10:43
  • Yes I have handled the dateTime diff, that is not the issue here. If I replace '[converted date]' in my sql query above with '#6/25/2019 04:34:27 PM#' or '6/25/2019 04:34:27 PM' it doesn't find the correct row because that is not in the right format. Even if I add '2019-06-25 16:34:27.713' it doesn't work. – NickyLarson Jul 02 '19 at 10:51
  • You need to use typed SQL parameters, not replaced string values. That's subject to SQL injection, and also doesn't work for date/time values, as you've discovered. Also, keep in mind `DATETIME` is only accurate up to 3 ms, so (e.g.) `'2019-06-25T16:34:27.713'` and `'2019-06-25T16:34:27.714'` cannot be distinguished. If you need millisecond precision (or better), use `DATETIME2`. – Jeroen Mostert Jul 02 '19 at 11:00
  • Do you use string Fields to store DateTime values? If so, change the Field type to DateTime. If not, don't store milliseconds and check for equality. – Jimi Jul 02 '19 at 11:04
  • Not sure I follow everything that has been said. I've converted it to Datetime like this so that the above sql statement ends in: '...AND RM_RATES_BY_QUANTITY_BAND.RATE_START_DATE = Convert(datetime, '6/25/2019 03:34:27.713 PM')' It doesn't error, but it doesn't return anything either – NickyLarson Jul 02 '19 at 11:56
  • Correct is `AND RM_RATES_BY_QUANTITY_BAND.RATE_START_DATE = @StartDate`, with `@StartDate` the name of a parameter you add as an `SqlParameter` instance to your `SqlCommand`, setting the `.Value` directly to your `DateTime` value with no need for string conversion. Any reason you can't use a properly parameterized query here? – Jeroen Mostert Jul 02 '19 at 12:26
  • I am using a proper parameterised query (it just isn't written above). I was trying to get it to work first directly in SSMS as a sql query so that I know it definitely works. '...AND RM_RATES_BY_QUANTITY_BAND.RATE_START_DATE = Convert(datetime, '2019-06-25 16:34:27.713')' Works, so now I just need to make sure that the parameterised query says exactly that. the issue is that although 'new DateTime(1970,1,1,0,0,0,0) is supposed to be in milliseconds, it's not displaying the milliseconds '.713' so I can't replicate the above sql statement in my query. – NickyLarson Jul 02 '19 at 13:02
  • If your parameter is of type `DATETIME` (which it will be, if the `.Value` is a `DateTime` object) it *will* pass milliseconds to the server, because the whole value will be sent over as a numeric value. It does not need to "display" anything; that only comes into play when values are converted into strings for debugging purposes. Check that you specify the type of your parameter correctly as a `SqlDbType.DateTime`. – Jeroen Mostert Jul 02 '19 at 13:11
  • Ok thanks, I did not realise that. As you can see in my edit to my post, it is a DateTime type so it should be working then. – NickyLarson Jul 02 '19 at 13:35
  • Don't store the milliseconds or query a range between `[DateTime]` and `[DateTime].AddSeconds(1)`. Or use a `DateTimeOffset` field. – Jimi Jul 02 '19 at 13:52

1 Answers1

0

you can use this function like

convert or cast https://www.w3schools.com/sql/func_sqlserver_cast.asp

Atik Vhora
  • 1
  • 4