Solved

COMPARE DATE AND TIME

Posted on 2012-03-30
11
317 Views
Last Modified: 2012-04-09
Hi All,

I have a function to compare date and time.

  Public Function Compare_Date_Time(ByVal strFieldToCompare As String, _
                                      ByVal dteDateTime As Date) As String

        Dim sbdSQLCommand As New System.Text.StringBuilder

        sbdSQLCommand.AppendLine("AND CONVERT(VARCHAR(8), " & strFieldToCompare & ", 112) = '" & Format(dteDateTime, "yyyyMMdd") & "'")
        sbdSQLCommand.AppendLine("AND CONVERT(VARCHAR(8), " & strFieldToCompare & ", 108) = '" & Format(dteDateTime, "HH:mm:ss") & "'")

        Return sbdSQLCommand.ToString

    End Function

It is work for any regional settings ?

Thank you.
0
Comment
Question by:emi_sastra
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37786537
Yes, becaus the 112 and 108 specifiers bypass the regional settings (ISO)
0
 
LVL 5

Expert Comment

by:Ronak Patel
ID: 37786543
The comparison of date and time will be dependent on database server's date and time. So whatever regional settings applied on the server it will compare based on that.

for example:
If the DB server is installed in your local machine, then it will consider your machine's regional settings.
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37786559
@Ronak_Patel: In this case you are comparing strings: it will always work, I think
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 37786572
Hi gpizzuto,

Format(dteDateTime, "HH:mm:ss") & "'")
Format(dteDateTime, "hh:mm:ss") & "'")

Which one will work any regional setting at database server ?
What makes it different ?

Thank you.
0
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37786625
Use the 114 format to have always 24 hours (cut out the milliseconds)
Use the "HH:mm:ss" format style to have the same in VB
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:emi_sastra
ID: 37787107
When sql show hh:mm:ss or HH:mm:ss ?

What regional setting makes it show like that ?

Thank you.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 37819180
Format 108 will be 24 hour clock, and should match with HH:mm:ss  (where as vb's hh:mm:ss is 12 hour clock and normally with 'tt' for am/pm).

So, SQL will handle yyyyMMdd and HH:mm:ss vb formats as strings when used with your current convert functions and the style codes.

But that is only formatting and looks like you should already be on the right track, so maybe there is a different problem ? e.g. There is more to regional settings like date/time offsets.

For that, you need to convert from your regional settings to those of the data as stored in the server (which might not be the local server settings). It can be a real problem if not done properly from the first place and in highly diverse regions where time offsets are important, would often resort to UTC date/times.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 37820739
Hi Mark,

Thanks for the explanation.
I just want to know how (at what regional setting) sql show HH:mm:ss ... or hh:mm:ss ....
It is important in order to know how I format the time to compare it.
Do I always format it into HH:mm:ss and it will works at any kind of setting.

Again, the time format shown in sql query result is based on what setting ?

Thank you.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 37821721
The regional settings on the machine running the instance of SQL Server doesnt really have too much to do with date/time.

There is a language setting that does give rise to a default date format - but that is more in recogniton of a date like 4/3/2012 (whether it is March or April).

So, there is that inherited language setting when you install SQL Server and that gives rise to things like the default format (e.g. dmy mdy etc) but the way you are using it, it is really the convert function and the style code that is the key to success.

You can check using @@LANGUAGE e.g. :
select @@language

And for more details :
exec sp_helplanguage @@language  -- (or simply exec sp_helplanguage)

And notice the dateformat. You can also set language and/or dateformat at runtime / execution time (not a permanent change). But it is not quite the same thing as regional settings.

In SQL2012 / denali there is the new FORMAT command that can also be used, but you are not there.

Basically SQL Server stores date times as numbers, so it is a special recognition of a decimal number and needs to be told how to be displayed. Thats what the convert function is for. The style code tells SQL Server what "format" should be used to display the number as a date or time.

So, if I do something simple like :
select getdate() -- > then I get --> 2012-04-08 20:14:41.660 --> which is different to the language settings of "US English"

But, if it do an implied date then the datedefault comes into play like :
select datename(month,'3/4/2012') -- then it shows March being compliant with mdy as per US English

In reality if I use the convert to explicitly tell SQL what to do, then I always get the right result e.g. :
select datename(month,convert(datetime,'3/4/2012',103)) -- and I get April because of the style code regardless of mdy

Similary with time. If I just give it a time format, then it will always assume 24 hour clock. I could provide the AM PM, or, must provide 24 hour clock format.

So, if you use the vb format of hh:mm:ss then you must also use AM/PM ie the 'tt' part of the format e.g. (using our same example directly above)
select convert(datetime,'3/4/2012 01:01:03 pm',103) -- shows the time as 13:01:03
--but:
select convert(datetime,'3/4/2012 01:01:03',103) -- shows the time as 01:01:03
--or using style code 108:
select convert(datetime,'01:01:03 pm',108) -- shows as 13:01:03
--and:
select convert(datetime,'01:01:03',108) -- shows as 01:01:03
--but what I really want is:
select convert(datetime,'20120403 13:01:03') -- shows time as 13:01:03
-- or even
select convert(datetime,'20120403 13:01:03 pm') -- shows time as 13:01:03 even with the redundant PM

So, from vb you need to always use the format that shows the correct time components - either include 'tt' e.g. "hh:mm:ss tt " or use 24 hour clock "HH:mm:ss"

Then using the convert function on the SQL side you will take explicit control over the date (number) datatype and present the results exactly as you want.

Now, that special number (date datatype) does have a level of precision that goes down to milliseconds, so, you might need to make sure you are controlling the precision (and it can depend on which date variant you are using in sql e.g. datetime2).

By limiting the size using the convert function, you again take control over that - in your case varchar(8) only leaves room for HH:mm:ss without the milliseconds.

On top of that, there is also a date independant format that SQL will always understand : yyyymmdd HH:mm:ss  (in 24 hour clock, or, add the am/pm) eg :
select convert(datetime,'20120403 13:01:03')
--or
select convert(datetime,'20120403 01:01:03 pm')

Will both be understood by SQL Server as to how to convert to a datetime datatype without having to use a style code. But always best to use a style code.

Now, there isnt a specific style code that says "hh:mm:ss" as a time is 24 hour clock - that is always implied unless there is a "PM" qualifier in the time that say it is 12 hour clock.

So, to answer your question, you should always use HH:mm:ss and it doesnt really matter about regional settings in terms of 12 or 24 hour clock. e.g.
select convert(datetime,'13:01:03',108)

Now that style code is used in two ways. It tells convert how to read a string, or, how to display a datetime data type as a string. Depends on what you are converting to and from. Convert really is for changing data types (like a datetime to a string or vice versa) and the style code is like the "format" command in VB.

It does matter about regional settings for GMT offsets but that is (a lot) more involved and a different question me thinks....
0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 37823164
Hi Mark,

Thank you very much for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now