1

I want to get Regional date and time format for SQL Server.

I am using Delphi7 here. My scenario is as below:

If my server's regional date format is (e.g.) yyyymmdd and client's SQL server is having mm/dd/yyyy (or any other valid date format) than query generated should contain proper date format.

Note: I have code written in delphi7 which set default format for MSSQL and will read server's regional date format too. And will convert the server's regional date format to MSSQL date format.

I Love Stackoverflow
  • 6,738
  • 20
  • 97
  • 216
  • 3
    No, query should contain bindable parameter rather than text value. – Victoria Aug 31 '17 at 04:48
  • I have code written in **delphi** which set default format for MSSQL. Which will convert the server's regional date format to MSSQL date format. – I Love Stackoverflow Aug 31 '17 at 04:52
  • 5
    I'm still missing a real reason why operate with text values. Why do you want to build SQL commands with date and time values represented as text? – Victoria Aug 31 '17 at 05:16
  • 1
    why don't you use parameters for your query, than the format of the datetime does not matters at all. Also read this http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput – GuidoG Aug 31 '17 at 07:52

5 Answers5

2

If my server's regional date format is (e.g.) yyyymmdd and client's SQL server is having mm/dd/yyyy (or any other valid date format) than query generated should contain proper date format.

You are making it to difficult for yourself.
The queries that you build do not have to care anything at all about how the regional format of your client machine or your sql server is setup.
There are language neutral formats that sql server will always understand, regardless of any regional setting.

You can find them here http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

So when you really want to build your query in text than use
"YYYYMMDD" for date, and "YYYYMMDD hh:mm:ss" ex('20170831 14:23:05') for datetime.
Now you don't have to care about the formats anymore.

Better would be offcourse to use parameters for your queries.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
GuidoG
  • 11,359
  • 6
  • 44
  • 79
0

In my opinion what you should do is get to know there is a standard format for your client's server and if it's standard then make your date in different variables i.e. not a single text/number format"yyyymmdd". Use different variables for each. And match them to each other by reading the format.

If the above is your problem then try this.

select SUBSTRING(Date,0,CHARINDEX('/',Date,0)) as Splitted

(in this Date is in client's format)

Or 

Month=SUBSTRING(Date,0,2)
Date=SUBSTRING(Date,3,2)
Year=SUBSTRING(Date,6,4)

Then show the date in any format you want.

Ash Man
  • 11
  • 3
  • We have a different clients on different geographical locations. Its not a single date format we will get. – I Love Stackoverflow Aug 31 '17 at 05:38
  • Using the same approach you can segregate the queries based on the location and format of date. – Ash Man Aug 31 '17 at 05:48
  • That's what my main question is, "How can I get regional date format of SQL Server using delphi7 Code?" – I Love Stackoverflow Aug 31 '17 at 05:49
  • 2
    I still don't get why you need that. If your code always uses this format "YYYYMMDD" than it does not matters how sql server is setup, this is a universal format that any sql server will understand – GuidoG Aug 31 '17 at 07:58
0

Try something like this assuming that the whole area has same format select * form data where Area="Abc"

Then search for ":" or "/"

Now making a substring of text segregated by ":" or "/" and getting a, b and c variables.

Since I am assuming same format acroos the area we can check if a > 12 and of two digits then it is a date, b is of two didgits and < 12 then month and if c has four digits then it's year. You might think if we then 01/01/2001 then what?

Thus I'm making assumption of all enteries from same place having a uniform format. And checking all enteries and get a sure shot answer by checking if all the conditions are met.

And if you handle a real time type of database then compare by today's date.

Ash Man
  • 11
  • 3
0

This query returns the date format used for your current session (@@language points to its language settings)

Select DateFormat 
From   sys.syslanguages 
Where  name = @@Language
Marc Guillot
  • 6,090
  • 1
  • 15
  • 42
0

Try Some thing like this in Query:

convert(varchar,DOB,103) as 'Date of Birth'

Abhishek Kanrar
  • 418
  • 4
  • 6