Date format when submitting queries

Hi, when submitting a query on a date field I need to query the date in american format (mm/dd/yy). When returning the date as part of another query it displays it in British format (dd/mm/yy) which is what I want. Regional settings etc are correct - what else should I be looking for?

Thanks,
Timthe great things about dates is that you can put them and view them in all types of ways.

If you want to be sure you are always in correct format just pick apart the date and using datepart. and you can grab the month, day, year, hour, minute and seconds and put them in any order. So have fun with it, dates just take a little time to get used to but they offer lots of help with date functions. DATEDIFF, DATEADD, DATEPART, DATENAME

ex:SELECT cast(DATEPART(day, GETDATE())as varchar(2))
+'/'+
cast(DATEPART(month, GETDATE())as varchar(2))
+'/'+
cast(DATEPART(year, GETDATE())as varchar(4))|||What interface are you using? Query analyzer? Access? VB?

If you run the following code in Query Analyzer, does it display March 4 ro April 3?

select Convert(varchar(20), cast('3/4/2003' as Datetime))

blindman|||Just try this
select convert( varchar(40),dateformat_field,101)|||i think he wants to enter the date in british format while entering it in the query.|||that's my problem too, i can convert the date as dd/mm/yyyy format when display, but when input the data, users are used dd/mm/yyyy format too, so when data is saved, error message will pop up. How can I convert the dd/mm/yyyy format into the mm/dd/yyyy format. Thanks for the help!|||that's my problem too, i can convert the date as dd/mm/yyyy format when display, but when input the data, users are used dd/mm/yyyy format too, so when data is saved, error message will pop up. How can I convert the dd/mm/yyyy format into the mm/dd/yyyy format. Thanks for the help!|||SET DATEFORMAT dmy

:)

Date format in Convert()

I'm trying to get smalldate from the date field in Sql table.
I want the format mm/dd/yyyy ( american format)

The query goes like this:
CONVERT([SmallDateTime],StatusDate,101)

still the result is:
2006-09-28 00:00:00

Krutika wrote:

I'm trying to get smalldate from the date field in Sql table.
I want the format mm/dd/yyyy ( american format)

The query goes like this:
CONVERT([SmallDateTime],StatusDate,101)

still the result is:
2006-09-28 00:00:00

convert(varchar,StatusDate,101)

It makes no sense to convert a datetime field to smalldatetime in a format unsupported. The result you've obtained is how the data is stored in a smalldatetime field. Can't do it any other way unless converting to varchar (or another character type)|||This works but I also want to retain the DateTime data type.

How do I do it in a query?|||

CONVERT(char(10),StatusDate,101)

hth

|||

Krutika wrote:

This works but I also want to retain the DateTime data type.

How do I do it in a query?

You can't... You can't store a mm/dd/yyyy format in a datatime/smalldatetime field. You can display it in the format you want with the example I gave, though.|||http://msdn2.microsoft.com/en-us/library/ms187819.aspx|||

here are all the formats:

Date Time in SQL SERVER

To get only the date

select convert(varchar, getdate(), 101)

12/15/2006

select convert(varchar, getdate(), 102)

2006.12.15

select convert(varchar, getdate(), 103)

15/12/2006

select convert(varchar, getdate(), 104)

15.12.2006

select convert(varchar, getdate(), 105)

15-12-2006

select convert(varchar, getdate(), 106)

15 Dec 2006

select convert(varchar, getdate(), 107)

Dec 15, 2006

select convert(varchar, getdate(), 110)

12-15-2006

To Get only the time :

select convert(varchar, getdate(), 108)

16:37:05

select convert(varchar, getdate(), 114)

16:37:05:120

To get Both date and time

select convert(varchar, getdate(), 100)

Dec 15 20064:38PM

select convert(varchar, getdate(), 109)

Dec 15 20064:38:41:197PM

select convert(varchar, getdate(), 112) --ANSI un-separated date format ->RECOMMENDED

20061215

select convert(varchar, getdate(), 113)

15 Dec 2006 16:41:11:937

|||http://www.sql-server-helper.com/tips/date-formats.aspx

Date and Language settings - SQL Server 2005

Despite the documentatin stating that SQL Server will pick up and use the locale settings, why will the database only accept entry in american (mdy) format?

MDY is the default datetime format in SQL Server.

You can change the format using "SET DATEFORMAT",

see help here: http://msdn2.microsoft.com/en-us/library/ms189491.aspx

Xinwei

|||Thanks,
I tried that and it didn't appear to do anything. I've got the ldefault language settings as British and all the examples were coming out in dmy displayed format despite changing the format.
The documentation (SQL Books) states that it should use the server locale.
I solved the problem using code to transform the date from a 'DateTime picker' into an acceptable format.
It was just an expression of frustration about the silly bugs in Microsoft products, especially the 2005 range (how about taking a comment as part of an enumeration, - VB 2005; not letting you move directly to row 0 in a BindingSource.DataSource, you have to MoveLast then MoveFirst - C# 2005; the ComboBox control spewing garbage out until it settles down, thus messing up any text or value changes; the setting the sorted property on a bound combobox sets the value returned to the position in the combobox list instead of the bound data value; The weird error messages often having nothing to do with the type and position of the error..... These are some of the recent value added features.