Date Picker formatting

Im sure we have all seen this error before:
"The value for the report parameter XXX is not valid for its type"

I have a report (RS 2005) that has 2 date parameters, "start" and "end".

My SQL in not very complicated at all, I have a simple WHERE date between @.start and @.end

But when I go to view the report in VS2005, I get the above error.

I am yet to find a decent fix for this, what is going on? the date I am trying is:
"26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.

I have changed my report Language setting to be en-AU, my local settings in Region Setting is English (Australia). (BTW, I had to change the Language setting in my report through the XML, is there a better way to do this?)

How do I fix this? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....

Hi,

I think the date/time picker sends the datetime parameter to the SQL query on the server using the regional settings of the client PC, and not the server settings. Then, the server will try to interpret this date using the server Regional Options. Can you specify both Regional Options of client and Reporting Server? If what I'm saying doesn't make sense, please let me know... Because I have users with different local settings, I have not been able to use the datetime picker, because I don't know in advance in what format I will receive it in my query...

Regards, Jeroen

|||I do know what you are saying. Thanks for that, it is what I assumed. I wonder why Microsoft did it this way? Surely it should be on a report by report basis...

I did however install the SP2 for SQL 2005 last night and it seemed to fix my problem with my regional settings. Now the datepicker works like it should. But I dont know if it was simply because I had to restart my PC for the update, or not.

Who knows, but either way I am going to have to manipulate the Reporting Servers regional settings. Unless they are already correct (which I am hoping they are).

Thanks for the tips

Date Picker formatting

Im sure we have all seen this error before:
"The value for the report parameter XXX is not valid for its type"

I have a report (RS 2005) that has 2 date parameters, "start" and "end".

My SQL in not very complicated at all, I have a simple WHERE date between @.start and @.end

But when I go to view the report in VS2005, I get the above error.

I am yet to find a decent fix for this, what is going on? the date I am trying is:
"26/02/2007" now, obviously its trying to us the en-US formatting, but im in Australia, so I want en-AU.

I have changed my report Language setting to be en-AU, my local settings in Region Setting is English (Australia). (BTW, I had to change the Language setting in my report through the XML, is there a better way to do this?)

How do I fix this? I have searched an searched but no-one seems to be able to give a clear answer as to what is going on....

Hi,

I think the date/time picker sends the datetime parameter to the SQL query on the server using the regional settings of the client PC, and not the server settings. Then, the server will try to interpret this date using the server Regional Options. Can you specify both Regional Options of client and Reporting Server? If what I'm saying doesn't make sense, please let me know... Because I have users with different local settings, I have not been able to use the datetime picker, because I don't know in advance in what format I will receive it in my query...

Regards, Jeroen

|||I do know what you are saying. Thanks for that, it is what I assumed. I wonder why Microsoft did it this way? Surely it should be on a report by report basis...

I did however install the SP2 for SQL 2005 last night and it seemed to fix my problem with my regional settings. Now the datepicker works like it should. But I dont know if it was simply because I had to restart my PC for the update, or not.

Who knows, but either way I am going to have to manipulate the Reporting Servers regional settings. Unless they are already correct (which I am hoping they are).

Thanks for the tipssql

Date formatting!

Just started looking at Reporting Services, I have created a report but
the date format I'm getting back is incorrect. I've seen something
that talks about .Net being responsible but I don't think I could code
anything to help. I'm not a programmer!
My statement returns the results as I would like in SQL, but clearly
doesn't apply to Reporting Services, as I'm getting an American format.
Code below for SQL which returns dd/mm/yy as I would like. How do I
got about getting this format into the report?
(invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
CONVERT(DATETIME, @.EndDate, 3))
Any help, gratefully received!
GaryIf you look at the rdl code for the report, and search for <Language>
you will find the language tag and notice that it has defaulted to
en-US. If you change it to en-GB you will get =A3 symbols and UK
formatted. If you still want to format the date further, you can use
FormatDateTime()
regards
weelin
On Oct 25, 12:43 pm, gdav...@.hotmail.com wrote:
> Just started looking at Reporting Services, I have created a report but
> the date format I'm getting back is incorrect. I've seen something
> that talks about .Net being responsible but I don't think I could code
> anything to help. I'm not a programmer!
> My statement returns the results as I would like in SQL, but clearly
> doesn't apply to Reporting Services, as I'm getting an American format.
> Code below for SQL which returns dd/mm/yy as I would like. How do I
> got about getting this format into the report?
> (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> CONVERT(DATETIME, @.EndDate, 3))
> > Any help, gratefully received!
> > Gary|||Hi,
The only safe solution I have found is to use "safe" format for
date-time as a string
yyyy-MM-dd (yyyy-MM-dd HH:mm:ss.mmm) and then to pass strings in
between reports.
CONVERT(VARCHAR(20),GETDATE(),120) -- from SQL
=Format(Now,"yyyy-MM-dd HH:mm:ss") -- inside reporting services
=Cdate("2006-10-25 14:15:00") -- string to date
This seems to work fine regardless of PC setups on the network.
The drawback is that you lose the date picker.
-- This one adds 16 hours to STRING parameter called TheDay
=format(DateAdd("h",16,cdate(Parameters!TheDay.Value)),"yyyy-MM-dd")
-- if you want to re-format string for display try
=Format(cdate("2006-10-25"),"dd/MM/yy") --October 25, 2005
I'm in Canada and working with British-American formats always ends up
with lots of errors and headache.
As a general rule I tend to use only two date formats whenever
possible:
1. 2006-10-25
2. October 25, 2006
Everything else is ambiguous.
Sincerely,
Damir
gdavid9@.hotmail.com wrote:
> Just started looking at Reporting Services, I have created a report but
> the date format I'm getting back is incorrect. I've seen something
> that talks about .Net being responsible but I don't think I could code
> anything to help. I'm not a programmer!
> My statement returns the results as I would like in SQL, but clearly
> doesn't apply to Reporting Services, as I'm getting an American format.
> Code below for SQL which returns dd/mm/yy as I would like. How do I
> got about getting this format into the report?
> (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> CONVERT(DATETIME, @.EndDate, 3))
> Any help, gratefully received!
> Gary|||Thanks for the replies guys, I eventually figured out that I needed MM
rather than mm. I do agree that using a date similar to 10 October
2006 would rule out any potential issues as this does seem to be
somewhat of a common problem.
Thanks
Gary
Damir wrote:
> Hi,
> The only safe solution I have found is to use "safe" format for
> date-time as a string
> yyyy-MM-dd (yyyy-MM-dd HH:mm:ss.mmm) and then to pass strings in
> between reports.
> CONVERT(VARCHAR(20),GETDATE(),120) -- from SQL
> =Format(Now,"yyyy-MM-dd HH:mm:ss") -- inside reporting services
> =Cdate("2006-10-25 14:15:00") -- string to date
> This seems to work fine regardless of PC setups on the network.
> The drawback is that you lose the date picker.
> -- This one adds 16 hours to STRING parameter called TheDay
> =format(DateAdd("h",16,cdate(Parameters!TheDay.Value)),"yyyy-MM-dd")
> -- if you want to re-format string for display try
> =Format(cdate("2006-10-25"),"dd/MM/yy") --October 25, 2005
> I'm in Canada and working with British-American formats always ends up
> with lots of errors and headache.
> As a general rule I tend to use only two date formats whenever
> possible:
> 1. 2006-10-25
> 2. October 25, 2006
> Everything else is ambiguous.
> Sincerely,
> Damir
> gdavid9@.hotmail.com wrote:
> > Just started looking at Reporting Services, I have created a report but
> > the date format I'm getting back is incorrect. I've seen something
> > that talks about .Net being responsible but I don't think I could code
> > anything to help. I'm not a programmer!
> >
> > My statement returns the results as I would like in SQL, but clearly
> > doesn't apply to Reporting Services, as I'm getting an American format.
> > Code below for SQL which returns dd/mm/yy as I would like. How do I
> > got about getting this format into the report?
> >
> > (invoice_date BETWEEN CONVERT(DATETIME, @.StartDate, 3) AND
> > CONVERT(DATETIME, @.EndDate, 3))
> >
> > Any help, gratefully received!
> >
> > Gary

Date formatting very flakey

We have a couple of dates displayed on our report.
The first comes from a string in our code, and I have a format
of 'mm/dd/yyyy'
The second comes from Globals!ExecutionTime, and I have a format of 'd'
These both display the way we want (which is with leading zeros) when
we stream the report to Excel.
But when we use the ReportViewer control, or when we stream the report
to html, we get strange stuff, like
00/03/2005 (s/b 09/03/2005) in the first case,
and
42/20/2005 (s/b 02/20/2006) in the second case. (Particularly
perturbing, since this is an RS global)Does using a capital m work properly? The lowercase m is for minute.
Check to see if "MM/dd/yyyy" works properly. I haven't had a problem
with the formatting and would be inetersted if this solved your
problem.
Regards,
Dan|||Ahh..much better, thanks!

Date formatting problems

Hi,

I have around 1000 records each with two dates in a database in MSDE onmy PC. I need to move the data to an on line SQL server and havetried to use Microsoft Web Data Administrator to do this. I canexport the data from the MSDE to an SQL file but it will not importbecause the date format in the SQL file is "dd,mm,yyyy".

If I export from either the MSDE or the SQL server both produce fileswith date in the format "dd,mm,yy" and yet I cannot import either!?!

It is impractical to change all the dates by hand. I am on abudget and do not have access to anything other than free software.

Can anyone advise me of the best way forward.

Thanks in anticipation.

MikeIf you have compatible versions of MSDE and SQL Server, you can detatch the msde database and reattach it to sql server without going through the export/import|||Hi

I am not sure if they are "compatable". The sql server is on myweb servers computer and I know little about it. I don't know howto attach or reattach tables but I will check on sql server 2000 bookson line.

Thanks

Mike|||

Mike,

Maybe you could import the data to a varchar column (instead of adatetime column), and write a small query to fix the formatting.

|||Hi

I have checked Books On Line and attaching a database does not sound tobe an option. I do not have access to the files of the SQLserver, but can access it through Web Data Administrator orADO.NET. Also, I want to add data to a single table withoutdisturbing the rest of my database.
Any one got any other ideas?

Mike|||Hi Geojan

That sounds an interesting idea. Not sure exactly how to do itbut I would have thought it should work OK. I could even writesome VB.NET code to do it for me (I am probably better with VB thanqueries). I am not usually quick at this sort of thing but willlet you know in the next day or two how I get on.

Thanks

Mike|||It pretty simple actually, say you have a Table (TblA), that's where you import your data, and the data column is defined as varchar(10) (named vc_Date).

And you have secound table (TblB), where the data column is defined as datetime (named dt_Date).

The sql statement needed to transfer the data from TblA to TblB, would be (Just for the example I added ColB, ColC and ColD):

Insert TabB (dt_Date, ColB, ColC, ColD)
Select Convert(datetime, Substring(Vc_Date, 7, 4) + Substring(Vc_Date, 4, 2) + Substring(Vc_Date, 1, 2))
,ColB, ColC, ColD
From TblA

If you like you can execute the statement form vb, or simply run it in query analyzer.

|||Hi Geojan

I ran the sql statement as an ExecuteNonQuery command and it worksgreat. My dates have been inserted correctly into the appropriatecolumns. Many thanks for the advice.

Mike|||

Thanks for the feedback Mike, nice to know it helped!

Date formatting problem

I have a crystal report that is populated by the results of a SQL stored procedure. The data returned is grouped by year and this appears along the x axis of the chart. The problem is that it sees the years as numbers and changes them to 2,000 2,001 2,002 etc. I have tried defining them as dates in the stored proc but this makes no difference and the chart options >grid >group axis does not allow me to alter the numbers category to date as it is greyed out.

Does anyone know how I can display these dates correctly.

Thanks for any help in advance.Try formatting the field to show without the comma.

Example in CR 8.5 using RDC... Right-click on the field and click Format, there's a tab called Date/Time or Number or something like that.|||I am also having this problem, expect I have used a formula field to combine two date fields to give me a date range (February 15-20, 2004), but I am getting February 15.00-20.00, 2,004.00. I can't change the formatting since the formula field is a string. Any suggestions?|||Try changing the default Number Format. In CR 8.5 using RDC, right-click to bring up the menu, Click Designer, Default Settings. Click the Fields tab. Click the Number button. This will allow you to set the default format for the Crystal Reports Program, not just the report you're working on.

date formatting or localization

I have written this ugly expression because I didn't know any other way. What I am trying to do is convert an English date string to a French string.

example

January 2005 > Janvier 2005

Thank you,

Pavel

=Switch(

Month(CDate(Fields!Month.Value) ) = 1, "Janvier" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 2, "Fevrier" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 3, "Mars" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 4, "Avril" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 5, "Mai" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 6, "Juin" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 7, "Juillet" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 8, "Aout" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 9, "September" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 10, "Octobre" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 11, "November" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 12, "December" & " " & Year(CDate(Fields!Month.Value))

)The only thing I see wrong with this is that you have Year(CDate(Fields!Month.Value) and that will not produce a result, correct?|||The field Month.Value stores "January 2004". I get the value from Analysis Services.

Pavel

Date formatting issue

I've Googled and MSDNed to no avail. All I want is to display, in a
concatenated expression with other text, say today's date as 2008-03-14,
regardless of the Windows local settings (I want to hard code the date
format). If all I had in a textbox were a date, I would use the Format
property and set it to yyyy-MM-dd. But I want an VB.NET function that will
take a date value as a parameter (and I suppose a second parameter like
"yyyy-MM-dd") and return 2008-03-14. Pretty simple, huh?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comFormat(Date,"yyyy-mm-dd")

Date Formatting in Drop Down List

I am trying to make a dropdownlist with a date. Where should I definethat I do want only the date, not the hour.
So far it shows somethinglike "14-02-2006 0:00:00" whereas I just want "14-02=2006". I tried"CAST(CONVERT (varchar(25); Ma_Fecha; 112) AS datetime)" in the SQLStatement but it doesnt seem to work.

[CODE]
Dim sSQL As String = " SELECT "
sSQL = sSQL & " Ma_Date,sDate "
sSQL = sSQL & " FROM vwMareaMain "
Dim comm As New SqlCommand(sSQL, objConn)
Dim dataAdapter As New SqlDataAdapter(comm)
dataAdapter.Fill(objDS2, "vwDate")

Me.cboDate.DataMember = "vwDate"
Me.cboDate.DataValueField = "Ma_Date"
Me.cboDate.DataSource = objDS2.Tables("vwDate").DefaultView
[/CODE]

do aCONVERT(varchar,datecolumn,101)

|||

me.cbodate.datatextfield="Ma_Date"

me.cbodate.datatextformatstring="{0:d}" or perhaps just "{d}"

Date Formatting in a Cross Tab

I am trying to create a cross tab that will show data from the current month (from before and after the current date) and grouped by month. I want it to automatically calculate the current month so that I do not have to change the date each month. I know how to group by month and I know how to get it to show everything greater than or equal to current date but I don't know how to do everything greater than or equal to current month. Is this possible?

Thanks,
Babs827Does anyone have any ideas?

Date formatting - Really newbie question

I have a unbelievably stupid problem that I can't figure out. My table is
imported from access and contains a column with data type datetime. I want
to be able to sum the data (which is a door counter for our store) to show
me all of the traffic for the day, then display the date as 1/1/2007 instead
of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
BOL help on this topic)...
convert(smalldatetime, [Date], 101)
No matter what I put in the style portion of the select statement it has no
impact on my output format. I suppose I could convert this to a varchar and
trim the results but that seems to be overkill (in addition to beiing a poor
solution).
Solved this. If anyone else is as inexperienced as I am and has this
problem, you can use the following method to change the output of the
datetime in this method.
Select CONVERT(varchar(20), [Date] as DT
will change this
1/30/2007 10:00:00
to this
1/30/2007
"Chuck G." wrote:

> I have a unbelievably stupid problem that I can't figure out. My table is
> imported from access and contains a column with data type datetime. I want
> to be able to sum the data (which is a door counter for our store) to show
> me all of the traffic for the day, then display the date as 1/1/2007 instead
> of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
> BOL help on this topic)...
> convert(smalldatetime, [Date], 101)
> No matter what I put in the style portion of the select statement it has no
> impact on my output format. I suppose I could convert this to a varchar and
> trim the results but that seems to be overkill (in addition to beiing a poor
> solution).
>
|||I don't quite see how that works (20 is too long, and the is only one
parenthesis). To
convert a datetime value to the same date at midnight, this is one solution:
dateadd(day,datediff(day,0,[Date]),0)
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Chuck G. wrote:
[vbcol=seagreen]
>Solved this. If anyone else is as inexperienced as I am and has this
>problem, you can use the following method to change the output of the
>datetime in this method.
>Select CONVERT(varchar(20), [Date] as DT
>will change this
>1/30/2007 10:00:00
>to this
>1/30/2007
>"Chuck G." wrote:
>

Date Formatting

My problem is with the paramater format date.

My code and data in SQL are showing the date correctly as dd/mm/yyyy.

When I run report in SSRS a couple of columns show the date mm/dd/yyyy.

The format option in all cells are set to 'd'. There is no difference between the properties on these cells.

The dates themselves seem to go wrong on the date "31/12/4000" and returns them as "12/31/4000".

Any help wqould be appreciated

You need to change the report properties language setting to: English (United Kingdom). It defaults to English(United States)

date formatting

hi
below is my query AdditionalField3 is varchar datatype .and in this field my date is
storing like this 12/05/06..now i wanted to change format of this field AdditionalField3
like 2006-12-5

how to do this?
SELECT *
FROM Tbl_CMS_UploadDetails
WHERE AdditionalField3 = '2006-12-5'

You can use the following statement..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Cast(AdditionalField3 as datetime) = Cast('2006-12-5' as datetime)

|||

i m getting below error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

|||Ok.. What is your date format 12/05/06|||yes and that field is varchar|||

Ok..

If you use dd/mm/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 103) = Cast('2006-12-5' as datetime)

if you use mm/dd/yy

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime)

|||

i tried both queries getting error

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

|||Is your AdditionalField3 has some invalid data?|||

yes this field also containing bank name ...we r using this field as common field so here for some cases we storing name and for other date thats why this field is varchar type ..for this purpose we r using format_id..

like this

for 83 we r storing date ..

SELECT *
FROM Tbl_CMS_UploadDetails
WHERE Convert(datetime, AdditionalField3, 101) = Cast('2006-12-5' as datetime) and format_id=83

|||

use the following query..

Code Snippet

--If you use dd/mm/yy

SET DATEFORMAT dmy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 103) Else NULL END = Cast('2006-12-5' as datetime)

--if you use mm/dd/yy

SET DATEFORMAT mdy

SELECT *

FROM Tbl_CMS_UploadDetails

WHERE Case When Isdate(AdditionalField3)=1 Then Convert(datetime, AdditionalField3, 101) Else NULL END = Cast('2006-12-5' as datetime)

|||

getting error

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'Then'.

|||Ooops Fixed , try now|||

again error

Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.

|||thanx a lot....gotted now..

Date formatting

Hi,
How do I get rid of the seconds in a date:
10/13/2004 8:27:00 PM
should read as
10/13/2004 8:27 PM
Thanks in advance
ChristianHi
Look at CAST and COVERT in BOL
Regards
Mike
"Christian Perthen" wrote:

> Hi,
> How do I get rid of the seconds in a date:
> 10/13/2004 8:27:00 PM
> should read as
> 10/13/2004 8:27 PM
> Thanks in advance
> Christian
>
>

Date formatting

Is there a function to return the date in the format of "4 July 2007" instead. I am returning rows which contain dates in the format "20/07/2007 10:48:16". Is there any function that convert, or will I have to write the code myself? I dont mind writing the code, but its a friday evening, and I am lazy and tired :-)
You can get close enough with CONVERT

eg, convert(varchar, getdate(),106)

To get the full month name though i believe you'd have to write something bespoke.

HTH.
|||

Yes. You have to write your own..

Select Cast(day(getdate()) as Varchar)

+ ' '

+ datename(month, getdate())

+ ' '

+ Cast(year(getdate()) as varchar)

|||As Mani indicated, create your own function, pass in the date, and get it back just like you want it.

Date Formatting

How can I format the current date as yyyy-mm-dd and display it in a text box on the report. The FormatDateTime function only allows certain NamedFormat for the short and long dates but does not satisfy the above requirements. Any ideas?

=Format(Globals!ExecutionTime,"yyyy/MM/dd")

|||Thanks, it worked!

Date formatting

Hi,
How do I get rid of the seconds in a date:
10/13/2004 8:27:00 PM
should read as
10/13/2004 8:27 PM
Thanks in advance
Christian
Hi
Look at CAST and COVERT in BOL
Regards
Mike
"Christian Perthen" wrote:

> Hi,
> How do I get rid of the seconds in a date:
> 10/13/2004 8:27:00 PM
> should read as
> 10/13/2004 8:27 PM
> Thanks in advance
> Christian
>
>

Date Formatting

I have a report with a date field and I am trying to add another field that will be 10 business days from the current field. So it needs to add 10 days while skipping weekends and holidays. Can anyone help??Maybe you could try a loop:

I don't know the right syntax, but you get the idea... Also, this doesn't count for Holidays.

DateVar dtmNewDate := {CurrentDate}
NumberVar DaysAdded := 1

Do Until DaysAdded = 11
dtmNewDate := DateAdd("D", 1, dtmNewDate)

If DayOfWeek (dtmNewDate) <> "Saturday" And DayOfWeek (dtmNewDate) <> "Sunday" Then
DaysAdded := DaysAdded + 1
End If

Loop

Date Formatting

Hi, all:
I'm testing SQL Server 2005 by using SQL Server 2000 database and supporting
software.
On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
yyyy-dd-mm and it's creating a problem with all the stored procedures and the
software.
Instead of changing the date format on all the afotware and stores
procedure, how can I change the date format in the server itself?
For instance, if I use SELECT GETDATE() I would get on the SQL Server 2000:
2007-10-11, and on the 2005 version I'm getting 2007-11-10.
Any ideas?
--
Thanks,
Rick.
"For every problem, there is a solution that is simple, neat, and wrong."
H. L. Mencken"yyyy-dd-mm doesn't make sense to me. It's not one of the date formats I
recognize (see CONVERT in BOL). However, I'm going to take a guess.
Check the default collation in the model database properties (and whichever
database you're testing in) and see if it's the same as the one on your 2000
box.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:EF08C898-D56B-462F-B40E-F1788F9E3A92@.microsoft.com...
> Hi, all:
> I'm testing SQL Server 2005 by using SQL Server 2000 database and
> supporting
> software.
> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
> yyyy-dd-mm and it's creating a problem with all the stored procedures and
> the
> software.
> Instead of changing the date format on all the afotware and stores
> procedure, how can I change the date format in the server itself?
> For instance, if I use SELECT GETDATE() I would get on the SQL Server
> 2000:
> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
> Any ideas?
> --
> Thanks,
> Rick.
> "For every problem, there is a solution that is simple, neat, and wrong."
> H. L. Mencken"|||You may change the date format using SET DATEFORMAT command.
More information on this with an example can be found at:
http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
"Rick" wrote:
> Hi, all:
> I'm testing SQL Server 2005 by using SQL Server 2000 database and supporting
> software.
> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
> yyyy-dd-mm and it's creating a problem with all the stored procedures and the
> software.
> Instead of changing the date format on all the afotware and stores
> procedure, how can I change the date format in the server itself?
> For instance, if I use SELECT GETDATE() I would get on the SQL Server 2000:
> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
> Any ideas?
> --
> Thanks,
> Rick.
> "For every problem, there is a solution that is simple, neat, and wrong."
> H. L. Mencken"|||Saleem,
He specified at the server level (which might even be the database level),
not in each procedure.
From SET DATEFORMAT BOL:
This setting is used only in the interpretation of character strings
as they are converted to date values. It has no effect on the
display of date values.
The setting of SET DATEFORMAT is set at execute or run time and not
at parse time.
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
> You may change the date format using SET DATEFORMAT command.
> More information on this with an example can be found at:
> http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
> --
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
> Register (Free):
> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>
> "Rick" wrote:
>> Hi, all:
>> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> supporting
>> software.
>> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
>> yyyy-dd-mm and it's creating a problem with all the stored procedures and
>> the
>> software.
>> Instead of changing the date format on all the afotware and stores
>> procedure, how can I change the date format in the server itself?
>> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> 2000:
>> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> Any ideas?
>> --
>> Thanks,
>> Rick.
>> "For every problem, there is a solution that is simple, neat, and wrong."
>> H. L. Mencken"|||Hi Jay, SET DATEFORMAT can be used at the server level.
You can check the current active settings by executing DBCC USEROPTIONS.
Pardon me if you think I did not understand the question.
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
"Jay" wrote:
> Saleem,
> He specified at the server level (which might even be the database level),
> not in each procedure.
> From SET DATEFORMAT BOL:
> This setting is used only in the interpretation of character strings
> as they are converted to date values. It has no effect on the
> display of date values.
> The setting of SET DATEFORMAT is set at execute or run time and not
> at parse time.
>
> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
> > You may change the date format using SET DATEFORMAT command.
> > More information on this with an example can be found at:
> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
> >
> > --
> > Thank you,
> > Saleem Hakani
> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> > Articles, SQL Clinic and a lot of SQL fun.
> > Register (Free):
> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
> >
> >
> > "Rick" wrote:
> >
> >> Hi, all:
> >>
> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
> >> supporting
> >> software.
> >>
> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
> >> yyyy-dd-mm and it's creating a problem with all the stored procedures and
> >> the
> >> software.
> >>
> >> Instead of changing the date format on all the afotware and stores
> >> procedure, how can I change the date format in the server itself?
> >>
> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
> >> 2000:
> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
> >>
> >> Any ideas?
> >>
> >> --
> >> Thanks,
> >>
> >> Rick.
> >>
> >> "For every problem, there is a solution that is simple, neat, and wrong."
> >> H. L. Mencken"
>
>|||> Pardon me if you think I did not understand the question.
I think Jay did understand the question. Here's a quote from the first post (the OP's post):
"For instance, if I use SELECT GETDATE() I would get on the SQL Server 2000:
2007-10-11, and on the 2005 version I'm getting 2007-11-10."
Clearly, this is a question of how datetime values are *displayed*. Not inperpreted for input. SET
DATEFORMAT has nothing to do with display of datetime data, since it is the client application that
converts the binary values returned by SQL Server into something human readable.
Rick,
You might wantr to check out: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
> Hi Jay, SET DATEFORMAT can be used at the server level.
> You can check the current active settings by executing DBCC USEROPTIONS.
> Pardon me if you think I did not understand the question.
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
> Register (Free):
> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>
> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time and not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored procedures and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and wrong."
>> >> H. L. Mencken"
>>|||SET DATEFORMAT mdy
select getdate()
Returns: 2007-10-12 06:40:47.090
SET DATEFORMAT dmy
select getdate()
Returns: 2007-10-12 06:40:47.090
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
> Hi Jay, SET DATEFORMAT can be used at the server level.
> You can check the current active settings by executing DBCC USEROPTIONS.
> Pardon me if you think I did not understand the question.
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
> Register (Free):
> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>
> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database
>> level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character
>> strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time and
>> not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005
>> >> it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored procedures
>> >> and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and
>> >> wrong."
>> >> H. L. Mencken"
>>|||OK Tibor, now I'm confused.
The OP's example was hand typed, not copy/pasted but suggested that the
default output of getdate() was changed. Having dealt with defaults changing
when language changes, it seemed reasonable. I simply never use the default
and let the server use its own internal format, then specify the output I
want - which is what your article seems to suggest (amongst a bazillion
other things :) I am also usually tunnel-visioned to us_english (yet another
failing of mine).
I tried changing the language from us_english (mdy) to british (dmy)
expecting the default output to change. It did not. Is it even possible to
change the default output?
select getdate()
-- do/set domething
select getdate()
-- get a different output format
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eFYCPoJDIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Pardon me if you think I did not understand the question.
> I think Jay did understand the question. Here's a quote from the first
> post (the OP's post):
> "For instance, if I use SELECT GETDATE() I would get on the SQL Server
> 2000:
> 2007-10-11, and on the 2005 version I'm getting 2007-11-10."
> Clearly, this is a question of how datetime values are *displayed*. Not
> inperpreted for input. SET DATEFORMAT has nothing to do with display of
> datetime data, since it is the client application that converts the binary
> values returned by SQL Server into something human readable.
> Rick,
> You might wantr to check out:
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
> news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
>> Hi Jay, SET DATEFORMAT can be used at the server level.
>> You can check the current active settings by executing DBCC USEROPTIONS.
>> Pardon me if you think I did not understand the question.
>> Thank you,
>> Saleem Hakani
>> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> Articles, SQL Clinic and a lot of SQL fun.
>> Register (Free):
>> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>>
>> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database
>> level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character
>> strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time and
>> not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in
>> message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server
>> > Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts,
>> > SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005
>> >> it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored procedures
>> >> and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and
>> >> wrong."
>> >> H. L. Mencken"
>>
>|||Let me start by quoting myself (from my prior post) ;-)
>> SET DATEFORMAT has nothing to do with display of datetime data, since it is the client
>> application that converts the binary values returned by SQL Server into something human readable.
Above holds. SQL Server sends a number of bits to the client app which interprets the bits and
displays them in a format (as a string) that we humans understand. These bits are *not* ASCII codes
with one letter for each part of the datetime format. I.e., the client app decides the format.
Most probably the OP used different client applications when connecting to the different servers.
Another thing that can alter datetime representation is whether the *client app* respects the
regional settings on the client machine. But it is still the client app.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:%23Lb3IhNDIHA.4228@.TK2MSFTNGP02.phx.gbl...
> OK Tibor, now I'm confused.
> The OP's example was hand typed, not copy/pasted but suggested that the default output of
> getdate() was changed. Having dealt with defaults changing when language changes, it seemed
> reasonable. I simply never use the default and let the server use its own internal format, then
> specify the output I want - which is what your article seems to suggest (amongst a bazillion other
> things :) I am also usually tunnel-visioned to us_english (yet another failing of mine).
> I tried changing the language from us_english (mdy) to british (dmy) expecting the default output
> to change. It did not. Is it even possible to change the default output?
> select getdate()
> -- do/set domething
> select getdate()
> -- get a different output format
> Thanks,
> Jay
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eFYCPoJDIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Pardon me if you think I did not understand the question.
>> I think Jay did understand the question. Here's a quote from the first post (the OP's post):
>> "For instance, if I use SELECT GETDATE() I would get on the SQL Server 2000:
>> 2007-10-11, and on the 2005 version I'm getting 2007-11-10."
>> Clearly, this is a question of how datetime values are *displayed*. Not inperpreted for input.
>> SET DATEFORMAT has nothing to do with display of datetime data, since it is the client
>> application that converts the binary values returned by SQL Server into something human readable.
>> Rick,
>> You might wantr to check out: http://www.karaszi.com/SQLServer/info_datetime.asp
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
>> news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
>> Hi Jay, SET DATEFORMAT can be used at the server level.
>> You can check the current active settings by executing DBCC USEROPTIONS.
>> Pardon me if you think I did not understand the question.
>> Thank you,
>> Saleem Hakani
>> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> Articles, SQL Clinic and a lot of SQL fun.
>> Register (Free):
>> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>>
>> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time and not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server 2005 it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored procedures and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and wrong."
>> >> H. L. Mencken"
>>
>>
>|||Is it possible your SQL Server 2005 is sitting on a server where the date is
set to November?
Brian|||Ya, that explains why I was confused. The OP got it wrong.
Thanks Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:23BF9BC8-4D32-40F2-949E-A845C921C7CA@.microsoft.com...
> Let me start by quoting myself (from my prior post) ;-)
>> SET DATEFORMAT has nothing to do with display of datetime data, since it
>> is the client application that converts the binary values returned by
>> SQL Server into something human readable.
> Above holds. SQL Server sends a number of bits to the client app which
> interprets the bits and displays them in a format (as a string) that we
> humans understand. These bits are *not* ASCII codes with one letter for
> each part of the datetime format. I.e., the client app decides the format.
> Most probably the OP used different client applications when connecting to
> the different servers.
> Another thing that can alter datetime representation is whether the
> *client app* respects the regional settings on the client machine. But it
> is still the client app.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23Lb3IhNDIHA.4228@.TK2MSFTNGP02.phx.gbl...
>> OK Tibor, now I'm confused.
>> The OP's example was hand typed, not copy/pasted but suggested that the
>> default output of getdate() was changed. Having dealt with defaults
>> changing when language changes, it seemed reasonable. I simply never use
>> the default and let the server use its own internal format, then specify
>> the output I want - which is what your article seems to suggest (amongst
>> a bazillion other things :) I am also usually tunnel-visioned to
>> us_english (yet another failing of mine).
>> I tried changing the language from us_english (mdy) to british (dmy)
>> expecting the default output to change. It did not. Is it even possible
>> to change the default output?
>> select getdate()
>> -- do/set domething
>> select getdate()
>> -- get a different output format
>> Thanks,
>> Jay
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eFYCPoJDIHA.5360@.TK2MSFTNGP03.phx.gbl...
>> Pardon me if you think I did not understand the question.
>> I think Jay did understand the question. Here's a quote from the first
>> post (the OP's post):
>> "For instance, if I use SELECT GETDATE() I would get on the SQL Server
>> 2000:
>> 2007-10-11, and on the 2005 version I'm getting 2007-11-10."
>> Clearly, this is a question of how datetime values are *displayed*. Not
>> inperpreted for input. SET DATEFORMAT has nothing to do with display of
>> datetime data, since it is the client application that converts the
>> binary values returned by SQL Server into something human readable.
>> Rick,
>> You might wantr to check out:
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in
>> message news:6D1081FD-9F92-4631-AEE0-605AED35A9F5@.microsoft.com...
>> Hi Jay, SET DATEFORMAT can be used at the server level.
>> You can check the current active settings by executing DBCC
>> USEROPTIONS.
>> Pardon me if you think I did not understand the question.
>> Thank you,
>> Saleem Hakani
>> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
>> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
>> Articles, SQL Clinic and a lot of SQL fun.
>> Register (Free):
>> http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>>
>> "Jay" wrote:
>> Saleem,
>> He specified at the server level (which might even be the database
>> level),
>> not in each procedure.
>> From SET DATEFORMAT BOL:
>> This setting is used only in the interpretation of character
>> strings
>> as they are converted to date values. It has no effect on the
>> display of date values.
>> The setting of SET DATEFORMAT is set at execute or run time
>> and not
>> at parse time.
>>
>> "Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in
>> message
>> news:7A0EF984-DAA0-45AA-940B-D2C8D2D4111D@.microsoft.com...
>> > You may change the date format using SET DATEFORMAT command.
>> > More information on this with an example can be found at:
>> > http://www.sqlcommunity.com/Default.aspx?grm2id=69&tabid=77
>> >
>> > --
>> > Thank you,
>> > Saleem Hakani
>> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server
>> > Community)
>> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts,
>> > SQL
>> > Articles, SQL Clinic and a lot of SQL fun.
>> > Register (Free):
>> > http://www.sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fHome%2ftabid%2f36%2fDefault.aspx
>> >
>> >
>> > "Rick" wrote:
>> >
>> >> Hi, all:
>> >>
>> >> I'm testing SQL Server 2005 by using SQL Server 2000 database and
>> >> supporting
>> >> software.
>> >>
>> >> On the old sever, date formatting was yyyy-mm-dd, on SQL Server
>> >> 2005 it's
>> >> yyyy-dd-mm and it's creating a problem with all the stored
>> >> procedures and
>> >> the
>> >> software.
>> >>
>> >> Instead of changing the date format on all the afotware and stores
>> >> procedure, how can I change the date format in the server itself?
>> >>
>> >> For instance, if I use SELECT GETDATE() I would get on the SQL
>> >> Server
>> >> 2000:
>> >> 2007-10-11, and on the 2005 version I'm getting 2007-11-10.
>> >>
>> >> Any ideas?
>> >>
>> >> --
>> >> Thanks,
>> >>
>> >> Rick.
>> >>
>> >> "For every problem, there is a solution that is simple, neat, and
>> >> wrong."
>> >> H. L. Mencken"
>>
>>
>>
>

Date formatting

Hi,
How do I get rid of the seconds in a date:
10/13/2004 8:27:00 PM
should read as
10/13/2004 8:27 PM
Thanks in advance
ChristianHi
Look at CAST and COVERT in BOL
Regards
Mike
"Christian Perthen" wrote:
> Hi,
> How do I get rid of the seconds in a date:
> 10/13/2004 8:27:00 PM
> should read as
> 10/13/2004 8:27 PM
> Thanks in advance
> Christian
>
>