Delphi7: Get SQL server system Date and Time format

1
votes

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.

5
sqlsql-serversql-server-2008delphidelphi-7
No, query should contain bindable parameter rather than text value. – Victoria
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. – Pratik Soni
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
why don't you use parameters for your query, than the format of the datetime does not matters at all. Also read this karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput – GuidoG

5 Answers

2
votes

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.

0
votes

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.

0
votes

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.

0
votes

Try Some thing like this in Query:

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

0
votes

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