CODEWITHSUNDEEP
sqlsql-serversql-server-2008delphidelphi-7
I Love Stackoverflow
I Love Stackoverflow

Reputation: 6868

Delphi7: Get SQL server system Date and Time format

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.

Upvotes: 1

Views: 1196

Answers (5)

GuidoG
GuidoG

Reputation: 12014

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.

Upvotes: 2

Marc Guillot
Marc Guillot

Reputation: 6455

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

Upvotes: 0

Abhishek Kanrar
Abhishek Kanrar

Reputation: 486

Try Some thing like this in Query:

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

Upvotes: 0

Ash Man
Ash Man

Reputation: 11

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.

Upvotes: 0

Ash Man
Ash Man

Reputation: 11

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.

Upvotes: 0

Related Questions