Android Question MS SQL Search by datetime

[David Hawkins]

Hi I have managed to connect to an MS SQL server successfully but now I want to search records based on date and datetime can you please help me with providing the SQL Server with the correct datetime type?

 

[mcqueccu]

Can you show a sample data of how the date and time is stored in the database?

 

[David Hawkins]

The MS SQL Server fields where the datatime is stored is as below

Column Name Data Type
DATEARRIVED datetime

Example data
DATEARRIVED COLLFROM REGISTRATION PARTNUMBER
2018-11-13 12:26:00.000 Company A YR64 NZF 212 0300

In MS SQL Server the date and Datetime can be stored in one of serveral data types i.e. date, datetime, datetime2(7), datetimeoffset(7). When searching using .Net you can use convert(datetime, '01/01/2018',103) or by using datetime parameters you can send the datetime as '01/01/2018'.

I hope this what you need.

 

[Computersmith64]

For date & time you could try something like:

SELECT *
FROM TABLENAME
WHERE
DATEARRIVED >= '2018-11-13T12:25:00.000' AND
DATEARRIVED <= '2018-11-13T12:27:00.000'

For just date:

SELECT *
FROM TABLENAME
WHERE
DATEARRIVED >= '2018-11-13T00:00:00.000' AND
DATEARRIVED <= '2018-11-14T00:00:00.000'

You can also use AM/PM:

SELECT *
FROM TABLENAME
WHERE
DATEARRIVED >= '2018-11-13 12:25:00 PM' AND
DATEARRIVED <= '2018-11-13 12:27:00 PM'

You can also use BETWEEN instead of >= & <= - so:

SELECT *
FROM TABLENAME
WHERE
DATEARRIVED BETWEEN '2018-11-13T12:25:00.000' AND '2018-11-13T12:27:00.000'

There are a bunch of date functions in T-SQL that will give you days, hours, etc - so I'd recommend looking up a T-SQL language reference for more info.

Try Googling "Searching dates with T-SQL" for more info.

- Colin.

 

[hatzisn]

Why don't you add the datetime search variables with parameters in .Net?

 

[hatzisn]

Also this VB.NET code (part of a class of mine - easily convertible to C# if you are a c# guy) will be extremelly useful to your case:

Public Shared Function GetAndroidDateFromDatetime(dt As DateTime) As Long
        Dim dtUnix As DateTime = New DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
        Dim ts As New TimeSpan
        ts = dt.Subtract(dtUnix)
        Return ts.TotalMilliseconds
    End Function

    Public Shared Function GetDatetimeFromAndroidDate(lngDate As Long) As DateTime
        Dim dtUnix As DateTime = New DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
        Return dtUnix.AddMilliseconds(lngDate)
    End Function