Solved

Is it possible to query database on date field using uk date format or do I use convert?

Posted on 2008-10-24
7
238 Views
Last Modified: 2012-05-05
I would like to be able to write a query using uk date format

select * from xxx where datefield >= '01/04/2008'

also is there a way to get dates returned from query displayed in uk format or
do I have to use convert in select statement?
0
Comment
Question by:deNZity
  • 2
  • 2
7 Comments
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
That query looks fine.  The default display format of the query likely depends on your installation settings and/or query tool configuation. If you want to request a specific format, by all means convert it to a string of the dexired form.  
0
 

Author Comment

by:deNZity
Comment Utility
Thanks for the reply,
the following query returns 8 rows where it should only return the last 2 rows.

select * from XXXX
where TransactionDate >= '01/08/2008'


2008-02-08 00:00:00.000
2008-02-18 00:00:00.000
2008-03-28 00:00:00.000
2008-04-10 00:00:00.000
2008-05-02 00:00:00.000
2008-07-16 00:00:00.000
2008-08-01 00:00:00.000
2008-08-29 00:00:00.000
0
 

Author Comment

by:deNZity
Comment Utility
My regional settings are set to english NZ which is the same as English UK
0
 
LVL 22

Accepted Solution

by:
dportas earned 250 total points
Comment Utility
Best practice is to use ISO format for date literals. They will work correctly under any regional settings. Use either:

SELECT * FROM xxx WHERE datefield >= '20080801';

or

SELECT * FROM xxx WHERE datefield >= '2008-08-01T00:00:00';

or

SELECT * FROM xxx WHERE datefield >= '2008-08-01T00:00:00.000';

Hard-coding dates in a query is rather unusual though. If you use datetime variables then you won't need to worry about formatting because datetime values don't have any format. It's only in the unusual case where you want to hard-code a date that you should have to resort to the ISO format.


0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 250 total points
Comment Utility
Under Server Properties, check your Default Language property.  That governs whether dates are interpreted as mdy (what you are getting) or dmy (what you desire).   US English is dmy.  You can present your dates in the mdy form or you can change the date format using "SET DATEFORMAT dmy"
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

16 Experts available now in Live!

Get 1:1 Help Now