• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:


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.
  • 4
  • 3
  • 2
  • +1
1 Solution
Yes, becaus the 112 and 108 specifiers bypass the regional settings (ISO)
Ronak PatelCommented:
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.
@Ronak_Patel: In this case you are comparing strings: it will always work, I think
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

emi_sastraAuthor Commented:
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.
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
emi_sastraAuthor Commented:
When sql show hh:mm:ss or HH:mm:ss ?

What regional setting makes it show like that ?

Thank you.
Mark WillsTopic AdvisorCommented:
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.
emi_sastraAuthor Commented:
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.
Mark WillsTopic AdvisorCommented:
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
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
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')
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....
emi_sastraAuthor Commented:
Hi Mark,

Thank you very much for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now