I am using British English for my database. I am using the following
function to convert the date
CONVERT(varchar(20),'04/10/2003',23)
and its returning me 2003-04-10 which I dont want. The format which I
require is
2003-10-04 (yyyy-mm-dd).
Any help will be much appreciated.
Thanking you in anticipation.
cheers,
Sunil SabirYour Date is ambigious to SQL Server. Even though you are using Britsh
English, the date will be stored in the correct format, however it will
convert your ambigious date defaulting the format to mm/dd/yyyy.
SELECT CONVERT(VARCHAR(20),CAST('10/4/2003' AS DATETIME), 23)
This is ambiguous and will return 2003-10-04
SELECT CONVERT(VARCHAR(20),CAST('10/APRIL/2003' AS DATETIME), 23)
This is not ambiguous and will return 2003-04-10
SELECT CONVERT(VARCHAR(20),CAST('04/10/2003' AS DATETIME), 23)
This is not ambiguous and will return 2003-04-10
Hope this helps
Andy
I would suggest either using a non ambiguous date format such as yyyy-mm-dd
"Sunil Sabir" wrote:
> Dear All,
> I am using British English for my database. I am using the following
> function to convert the date
> CONVERT(varchar(20),'04/10/2003',23)
> and its returning me 2003-04-10 which I dont want. The format which I
> require is
> 2003-10-04 (yyyy-mm-dd).
> Any help will be much appreciated.
> Thanking you in anticipation.
> cheers,
> Sunil Sabir
>|||Hi
Have a look at: http://www.karaszi.com/sqlserver/info_datetime.asp
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sunil Sabir" wrote:
> Dear All,
> I am using British English for my database. I am using the following
> function to convert the date
> CONVERT(varchar(20),'04/10/2003',23)
> and its returning me 2003-04-10 which I dont want. The format which I
> require is
> 2003-10-04 (yyyy-mm-dd).
> Any help will be much appreciated.
> Thanking you in anticipation.
> cheers,
> Sunil Sabir
>|||hi
Probably yo can try this
SET DATEFORMAT dmy
SELECT convert (varchar(20), cast('04/10/2003' as datetime),23)
Probable this solves the problem. the key here is SET DATEFORMAT dmy
please revert back if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sunil Sabir" wrote:
> Dear All,
> I am using British English for my database. I am using the following
> function to convert the date
> CONVERT(varchar(20),'04/10/2003',23)
> and its returning me 2003-04-10 which I dont want. The format which I
> require is
> 2003-10-04 (yyyy-mm-dd).
> Any help will be much appreciated.
> Thanking you in anticipation.
> cheers,
> Sunil Sabir
>|||> I am using British English for my database. I am using the following
> function to convert the date
> CONVERT(varchar(20),'04/10/2003',23)
Are you really hard-coding this date from 2+ years ago in your statement?
Or is your real query working against a table?
If you are hard-coding the statement, you should always use unambiguous date
formats that are language-neutral, are not affected by regional settings,
and are safe from dateformat settings (never mind absolutely clear to every
human who looks at them, regardless of which country they are from).
If you are really working against a table, you should be clear that in
DATETIME / SMALLDATETIME columns, the date is NOT stored in the format you
describe, even though that may be what Enterprise Manager or other graphical
query tools will present to you.
> and its returning me 2003-04-10 which I dont want. The format which I
> require is
> 2003-10-04 (yyyy-mm-dd).
So either enter the date in an unambiguous way:
DECLARE @.d SMALLDATETIME
SET @.d = '20031004'
SELECT CONVERT(CHAR(10), @.d, 120)
Or else explicitly convert it first:
DECLARE @.d SMALLDATETIME
SET @.d = CONVERT(DATETIME, '04/10/2003', 103)
SELECT CONVERT(CHAR(10), @.d, 120)
Please have a look at these articles:
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp|||Dear SIr,
I tried to follow your instructions but it still showing me 2003-04-10. I
tried changing the DATEFORMAT but same problem. any more ideas. I am passin
g
the date as a parameter through a stored procedure below is the syntax
''''+CONVERT(varchar(20),CAST(@.inputTrai
ningDate as datetime),23)+'''
Your help would be greatly appreciated.
thanks,
Sunil Sabir
"Chandra" wrote:
> hi
> Probably yo can try this
> SET DATEFORMAT dmy
> SELECT convert (varchar(20), cast('04/10/2003' as datetime),23)
> Probable this solves the problem. the key here is SET DATEFORMAT dmy
> please revert back if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Sunil Sabir" wrote:
>|||gi sunil
''''+CONVERT(varchar(20),CAST(@.inputTrai
ningDate as datetime),23)+'''
works fine. If @.inputTrainingDate is of type date, then u need not convert
it here
it can be written as
''''+CONVERT(varchar(20), @.inputTrainingDate ,23)+'''
see that you use "SET DATEFORMAT dmy"
in the stored procedure.
just see this:
declare @.date as datetime
SET DATEFORMAT dmy
SET @.date = '04/10/2003'
SELECT CONVERT(carchar(20), @.date, 23)
displays: 2003-10-04
please let me know if u still have a problem.
just send the SP script if u have a problem
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sunil Sabir" wrote:
> Dear SIr,
> I tried to follow your instructions but it still showing me 2003-04-10. I
> tried changing the DATEFORMAT but same problem. any more ideas. I am pass
ing
> the date as a parameter through a stored procedure below is the syntax
> ''''+CONVERT(varchar(20),CAST(@.inputTrai
ningDate as datetime),23)+'''
> Your help would be greatly appreciated.
> thanks,
> Sunil Sabir
> "Chandra" wrote:
>|||Dear Sir,
Dear Sir,
Thanks for your prompt reply. The problem is that when I execute SET DATE
FORMAT before the stored procedure it works fine and shows me 2003-10-04 .if
i want to retrieve any records from the DB with respect to this date. It
doest show me anything in the results pane of the QUERY analyzer. For exampl
e
select first name from database where date = '2003-10-04'
But when i post the above query in the VIEW of Enterprise manager it does
show me records. and the strange thing is that when I change the date back t
o
2003-04-10 in the query analyzer it starts working. I am very
dontknow whats happening.
Please help me . I will be very grateful to you.
cheers,
Sunil Sabir
"Chandra" wrote:
> gi sunil
> ''''+CONVERT(varchar(20),CAST(@.inputTrai
ningDate as datetime),23)+'''
> works fine. If @.inputTrainingDate is of type date, then u need not conver
t
> it here
> it can be written as
> ''''+CONVERT(varchar(20), @.inputTrainingDate ,23)+'''
> see that you use "SET DATEFORMAT dmy"
> in the stored procedure.
> just see this:
> declare @.date as datetime
> SET DATEFORMAT dmy
> SET @.date = '04/10/2003'
> SELECT CONVERT(carchar(20), @.date, 23)
> displays: 2003-10-04
> please let me know if u still have a problem.
>
> just send the SP script if u have a problem
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Sunil Sabir" wrote:
>|||Egads. Stop passing in bad date formats and your problem will disappear.
"Sunil Sabir" <SunilSabir@.discussions.microsoft.com> wrote in message
news:178212DA-215A-4050-A6F4-975CD9520E21@.microsoft.com...
> Dear Sir,
> Dear Sir,
> Thanks for your prompt reply. The problem is that when I execute SET DATE
> FORMAT before the stored procedure it works fine and shows me 2003-10-04
> .if
> i want to retrieve any records from the DB with respect to this date. It
> doest show me anything in the results pane of the QUERY analyzer. For
> example
> select first name from database where date = '2003-10-04'
> But when i post the above query in the VIEW of Enterprise manager it does
> show me records. and the strange thing is that when I change the date back
> to
> 2003-04-10 in the query analyzer it starts working. I am very
> dont
> know whats happening.
> Please help me . I will be very grateful to you.
> cheers,
> Sunil Sabir
>
> "Chandra" wrote:
>|||So how do I do it'
Please Help
"Aaron Bertrand [SQL Server MVP]" wrote:
> Egads. Stop passing in bad date formats and your problem will disappear.
>
> "Sunil Sabir" <SunilSabir@.discussions.microsoft.com> wrote in message
> news:178212DA-215A-4050-A6F4-975CD9520E21@.microsoft.com...
>
>sql
![Wink [;)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sCTsbxmbpyl1RFn5L0VdYpE2dLJEW7OSyLPSt7oMI7rr53sJ-1B74NCVyxlkF-dI8qkbSYtrMwglTRPaRFXWNTcsr5iCxL6x7EA29PIz4ISxuwbDOD6g=s0-d)
![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_tEoCAtbY4p9uM08vI7e-ZrG2V-hFSdNSDcjDEJ7No64z9IJA4mlboID7uuyNySi0tu_7rVBoSe-uWw_Gzq8gksvZvX_Yw_QUjexVx5E-ZLShML_s8EaA=s0-d)