Dattime format problems when retrieved on FreeBSD server

I'm using MSSQL with PHP and this works fine on a Windows server.

When i move to a FreeBSD server, the date formatting is not working.

FreeBSD retrieves the date as: mon dd yyyy hh:mi:ss:mmmAM - and php's functions for formatting date fails.

I've tried using: Convert(varchar(10), Date, 103) AS Date, and the date is formatted fine - BUT sorting on date does NOT work.

Are there any way i can do changes to datetime behaviour on server side? I NEVER wants the date in mon dd yyyy hh:mi:ss:mmmAM. I don't need milliseconds, and i want 24h format - not AM/PM. Are there any settings on the SQL server for this?

Where are you doing your sorting? With T-SQL CONVERT-style affects sort:

ORDERBYConvert(nvarchar(30), OrderDate, 109)

|||

I'm not sure what you mean. I SELECT Convert(varchar(10), Date, 103) AS Date, and last sentence is ORDER BY Date DESC.

Anyway - i would prefer to fix this server-side if possible, so that i don't need to rewrite all queries adding convert functions etc...

|||If you want to ORDER BY the output of Convert(varchar(10), Date, 103) then you have to put that expression in the ODER BY clause not simply the column named Date.|||

I'll try that, but i don't think it will work, because it returns the date as a string, and thus sorting the strings from highest numbers, to lower, which does not not necessarily need to be correct(?)

Is this really the only reason to make it work?

Dattime format problems when retrieved on FreeBSD server

I'm using MSSQL with PHP and this works fine on a Windows server.

When i move to a FreeBSD server, the date formatting is not working.

FreeBSD retrieves the date as: mon dd yyyy hh:mi:ss:mmmAM - and php's functions for formatting date fails.

I've tried using: Convert(varchar(10), Date, 103) AS Date, and the date is formatted fine - BUT sorting on date does NOT work.

Are there any way i can do changes to datetime behaviour on server side? I NEVER wants the date in mon dd yyyy hh:mi:ss:mmmAM. I don't need milliseconds, and i want 24h format - not AM/PM. Are there any settings on the SQL server for this?

Where are you doing your sorting? With T-SQL CONVERT-style affects sort:

ORDERBYConvert(nvarchar(30), OrderDate, 109)

|||

I'm not sure what you mean. I SELECT Convert(varchar(10), Date, 103) AS Date, and last sentence is ORDER BY Date DESC.

Anyway - i would prefer to fix this server-side if possible, so that i don't need to rewrite all queries adding convert functions etc...

|||If you want to ORDER BY the output of Convert(varchar(10), Date, 103) then you have to put that expression in the ODER BY clause not simply the column named Date.|||

I'll try that, but i don't think it will work, because it returns the date as a string, and thus sorting the strings from highest numbers, to lower, which does not not necessarily need to be correct(?)

Is this really the only reason to make it work?

Datetime to Time Only

Is it possible in SRS 2K to use a String.Format to change a field of
MM/DD/YYYY HH:MM:SS to display as HH:MM AM/PM only?
ThanksIt is possible. Just go to the properties of the field/ Textbox where you
want to be in time format and go to Format tab. and select format code and
click on "..." you can see time format just select it or it is basically "t"
format.
Amarnath
"lucotc" wrote:
> Is it possible in SRS 2K to use a String.Format to change a field of
> MM/DD/YYYY HH:MM:SS to display as HH:MM AM/PM only?
>
> Thanks

DATETIME To Format mm/dd/yyyy hh:mm am/pm

I have a column in a database set as a DATETIME datatype, when I select it, I want to return it as:

mm/dd/yyyy hh:mm am or pm.

How in the world can I do this? I looked at the function CONVERT() and it doesnt seem to have this format as a valid type. This is causing me to lose my hair, in MySQL it is just so much easier. .

At any rate, currently when I select the value without any convert() it returns as:

June 1 2007 12:23AM

Which is close, but I want it as:

06/01/2007 12:23AM

Thanks!

Hi,

Normally you would be formatting your date on the UI or Report side, which means you must let your program display it correctly or let your reporting engine format your date as you want to.

But if you really want to change the format of your date, you can do this by changing the type to string by using the CONVERT function. The closest that I can come up with is this format:

mm/dd/yyyy

checki it here (code 101):

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

the syntax would be like this in SQL

SELECT CONVERT('your date', nvarchar(MAX), 101)

Maybe you can mix it up with code 108 so that you can concatenate the time with it.

cheers,

Paul June A. Domag

|||

The reason I am formatting it in SQL is this is inside a trigger written in pure SQL which emails from the DB.

Yeah CONVERT() using type 101 was the closest I could get as well, but it only displays date, no time. I need both date and time in the format I specified above.

This is absolutley stupid that they did it this way, they should take a lession from MySQL which allows you to format a DATETIME in any fashion via strings such as %m/%d/%Y etc, etc.

Anybody have other ideas?

|||

Hi,

Why not just combine the codes 101 and 108 and maybe manually parse it using substring?

You can create a scalar function to make it reusable.

Code Snippet

DECLARE @.dt VARCHAR(MAX)

SELECT @.dt = CONVERT(nvarchar(MAX), GETDATE(), 101) + ' ' + CONVERT(nvarchar(MAX), GETDATE(), 108)

-- After this just use Substring to satisfy your formatting

note: varchar(max) is only available in SQL2005. specify the lenght if your using SQL2000

cheers,

Paul June A. Domag

|||

As Paul indicated, formating is normally left to the client application. I suspect the developer you are working with either does not know how to properly format for display in his/her application, or is too lazy and is passing the responsibility off to the database.

This expression should provide the date in the form you desire. Replace the [ @.MyDate ] with your column or date value. You can easily create your own function that will do this for you so that you can re-use this expression.

DECLARE @.MyDate datetime

SET @.MyDate = '2007/07/21 11:35:45.255PM'

SELECT MyDate =
convert( varchar(10), @.MyDate, 101) +
stuff( right( convert( varchar(26), @.MyDate, 109 ), 15 ), 7, 7, ' ' )

MyDate
-
07/21/2007 11:35 PM

|||

Arnie Rowland ,

You are the man, that worked like a charm. I guess my problem with the convert function is that there is no predefined format of:

mm/dd/yyyy hh:mm am/pm

I would assume this is very very popular, so I am confused as to why it is not implemented. As far as this benig done on the front end, this has to be done at the DB level, since we send emails out via the database.

|||

I have to say that I wouldn't want to send an email from a trigger that required any kind of special formatting. Perhaps an alert to a sysadmin, but if I was going to send correspondence like that, I would put my information in a queue of some sort and have a tool to send the email.

the CONVERT thing is a mess because it doesn't give you enough formats, unlike a proper data presentation layer would. I have (in the past) used datePart to build up a date formatter of my own, or you could probably do it with the CLR quite nicely. But SQL Server should be used to manage and manipulate data, not format it (as a broad rule of course. We all do it from time to time to appease a user/manager/programmer etc, so don't think I am saying it is horrible, it just isn't as ideal as using a programming tool made to do such things.)

Datetime query issue with C# stored procedure

Ok, i am using the convert function to get the date format from my datetime column. My problem is when C# try's to pull the column name the column name is not sent in the query. When I run the query in the query analyzer the column name is blank in the result window. How can I get the date from the record with out losing the name of my column in the query. My data binding needs the name of the column to bind the data to the drop down list control. Here is the statement:

SQL statement
SELECT DISTINCT convert(datetime, eventDT, 110) FROM tblRecognition

C# code
ddlDateTo.DataSource = _uiCode.Fill.Date();
ddlDateTo.DataTextField = "eventDT";
ddlDateTo.DataBind();

Thank you,assign a column alias in your SELECT

convert(datetime, eventDT, 110) as displaydate

ddlDateTo.DataTextField = "displaydate";|||well that was simple lol. thank you, i don't know why that didn't accur to me.

DateTime problem

I need to UPDATE DateTime in database. But input paramter (@.DatumPozadovany) is not in Default format 'mon dd yyyy hh:miAM', but in Europian one 'dd mon yyyy HH:mi'.

This code don't work, becuse it converts standart input into unstandart output. I need the oposite of it.

UPDATE DoslaObjednavka SET DatumPozadovany = CONVERT(datetime, @.DatumPozadovany, 13), Oznaceni = @.Oznaceni WHERE (Id = @.Id)

Please, help.

SELECT CONVERT(DATETIME, GETDATE(),109)

--returns 2007-02-12 21:09:56.970

SELECT CONVERT(nvarchar(26), GETDATE(),109)

--returns Feb 12 2007 9:09:56:970PM

From the SQL Server 2005 Books Online topic
CAST and CONVERT (Transact-SQL)
"In the following table, the two columns on the left represent the style values for converting datetime or smalldatetime data to character data. Add 100 to a style value to obtain a four-place year that includes the century (yyyy).

|||Interestingly, you should try without the convert. If you let SQL do the conversion implicitly, it may well recognise the format you're using anyway. If you specify a format, it will have to match it exactly.

Rob|||Thaks everyone for reply. Error was at very different place.
In ASP.NET i didn't specify parametr datatype. It expected some strange datetime format and didn't work. Now it works fine.

DateTime parameter without time part

There is a post that tells how to do this:
>>Use =Today() and set the field format to d
but where do you set the field format of a parameter?
--
Michael White
Programmer/Analyst
Marion County, ORHave you tried inserting the parameter on the report and then right click
properties and set format?
Another option is using code file.
"Michael" <xxx.xxx.xxx> wrote in message
news:%23g16Kjn%23EHA.1392@.tk2msftngp13.phx.gbl...
> There is a post that tells how to do this:
>>Use =Today() and set the field format to d
> but where do you set the field format of a parameter?
> --
> Michael White
> Programmer/Analyst
> Marion County, OR
>|||I don't think you can set the format of a parameter past the fact that it is
a date, but the time does NOT have to be entered when using the parameter.
Some people choose to use the string data type instead of date...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Michael" <xxx.xxx.xxx> wrote in message
news:%23g16Kjn%23EHA.1392@.tk2msftngp13.phx.gbl...
> There is a post that tells how to do this:
> >>Use =Today() and set the field format to d
> but where do you set the field format of a parameter?
> --
> Michael White
> Programmer/Analyst
> Marion County, OR
>|||Wayne described what I do. I create the parameter as a string. My stored
procedures then convert the string to datetime datatypes. If you don't do
somthing similar, the parameter fields on the report append hh:mm:ss to the
parameter value supplied.
Mardy
"Wayne Snyder" wrote:
> I don't think you can set the format of a parameter past the fact that it is
> a date, but the time does NOT have to be entered when using the parameter.
> Some people choose to use the string data type instead of date...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Michael" <xxx.xxx.xxx> wrote in message
> news:%23g16Kjn%23EHA.1392@.tk2msftngp13.phx.gbl...
> > There is a post that tells how to do this:
> >
> > >>Use =Today() and set the field format to d
> >
> > but where do you set the field format of a parameter?
> >
> > --
> > Michael White
> > Programmer/Analyst
> > Marion County, OR
> >
> >
>
>

DateTime parameter switches format

The reports I am creating in a VS2005 Business Intelligence Project
have DateTime parameters. To preview reports, after the parameters have
been entered in "Preview" tab, the "View Report" button has to be
clicked.
It looks to me that whenever the "View Report" is cliked, it causes
"datetime" parameter boxes to re-oder the dates so that "mm" and "dd"
values are interchanged.
For instance, if I enter "12/02/2006" (meaning Feb 02, 2006, in
Australian format ie. dd/mm/yyyy) and click "View Report" button, the
datatime parameter is rearranged as "02/12/2006", and consequenly
producing data for Dec 02, 2006.
Strangely, If I click "view report" again, it interchanges "dd" and
"mm" values again resulting the date I originally intended and produces
the correct report. Clicking "View Report" again causes "dd" and "mm"
to interchange, and so on it goes.
If I enter "20/02/2006" in the datetime parameter box, and click "View
Report" button, I get an error message about incorrect datetime format.
"An error occured during local report processing. The value provided
for the report parameter 'dtStartDate' is not valid for its type".
So, it appears that VS2005
* expects me to input datetime as "mm/dd/yyyy" format,
* rearranges "mm" and "dd" to "dd/mm/yyyy" format (whenever "View
Report" button is clicked) before passing the parameters to the stored
procedures.
I have set the report's "Language" property to "Australia", and WinXP's
language to "Australian English"
Any suggestions and workarounds are greatly appreciated.
Thanks
SurOne thing you can do is to change the parameter type to string, where you can
control the string format of the date... The down side to this is that you do
NOT get the Date-picker window...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"suranga.suranga@.gmail.com" wrote:
> The reports I am creating in a VS2005 Business Intelligence Project
> have DateTime parameters. To preview reports, after the parameters have
> been entered in "Preview" tab, the "View Report" button has to be
> clicked.
> It looks to me that whenever the "View Report" is cliked, it causes
> "datetime" parameter boxes to re-oder the dates so that "mm" and "dd"
> values are interchanged.
> For instance, if I enter "12/02/2006" (meaning Feb 02, 2006, in
> Australian format ie. dd/mm/yyyy) and click "View Report" button, the
> datatime parameter is rearranged as "02/12/2006", and consequenly
> producing data for Dec 02, 2006.
> Strangely, If I click "view report" again, it interchanges "dd" and
> "mm" values again resulting the date I originally intended and produces
> the correct report. Clicking "View Report" again causes "dd" and "mm"
> to interchange, and so on it goes.
> If I enter "20/02/2006" in the datetime parameter box, and click "View
> Report" button, I get an error message about incorrect datetime format.
> "An error occured during local report processing. The value provided
> for the report parameter 'dtStartDate' is not valid for its type".
> So, it appears that VS2005
> * expects me to input datetime as "mm/dd/yyyy" format,
> * rearranges "mm" and "dd" to "dd/mm/yyyy" format (whenever "View
> Report" button is clicked) before passing the parameters to the stored
> procedures.
> I have set the report's "Language" property to "Australia", and WinXP's
> language to "Australian English"
> Any suggestions and workarounds are greatly appreciated.
> Thanks
> Sur
>|||I had what sounds very like this problem as described in an earlier thread.
If it is the same problem, it should not occur when reports are deployed to
the report server. For me, it only occurs in development.
Ed Allison
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:F8583B54-267B-40F7-B105-D450934E8F53@.microsoft.com...
> One thing you can do is to change the parameter type to string, where you
> can
> control the string format of the date... The down side to this is that you
> do
> NOT get the Date-picker window...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "suranga.suranga@.gmail.com" wrote:
>> The reports I am creating in a VS2005 Business Intelligence Project
>> have DateTime parameters. To preview reports, after the parameters have
>> been entered in "Preview" tab, the "View Report" button has to be
>> clicked.
>> It looks to me that whenever the "View Report" is cliked, it causes
>> "datetime" parameter boxes to re-oder the dates so that "mm" and "dd"
>> values are interchanged.
>> For instance, if I enter "12/02/2006" (meaning Feb 02, 2006, in
>> Australian format ie. dd/mm/yyyy) and click "View Report" button, the
>> datatime parameter is rearranged as "02/12/2006", and consequenly
>> producing data for Dec 02, 2006.
>> Strangely, If I click "view report" again, it interchanges "dd" and
>> "mm" values again resulting the date I originally intended and produces
>> the correct report. Clicking "View Report" again causes "dd" and "mm"
>> to interchange, and so on it goes.
>> If I enter "20/02/2006" in the datetime parameter box, and click "View
>> Report" button, I get an error message about incorrect datetime format.
>> "An error occured during local report processing. The value provided
>> for the report parameter 'dtStartDate' is not valid for its type".
>> So, it appears that VS2005
>> * expects me to input datetime as "mm/dd/yyyy" format,
>> * rearranges "mm" and "dd" to "dd/mm/yyyy" format (whenever "View
>> Report" button is clicked) before passing the parameters to the stored
>> procedures.
>> I have set the report's "Language" property to "Australia", and WinXP's
>> language to "Australian English"
>> Any suggestions and workarounds are greatly appreciated.
>> Thanks
>> Sur
>>|||Ed and Wayne,
Thanks. I published the reports at a Reporting Server instance; it
doesn't suffer from this bug. I guess until a service pack is realised
for VS2005, I'll have to enter dates in the universal format
(yyyy-MM-dd) to overcome this bug.

DateTime Parameter need yyyy-mm-dd format

I have a parm defined as DateTime in RS2005 (so you see the calendar control
for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
display yyyy-mm-dd as it was when we had date defined as string rather than
DateTime. I went into regional settings on my pc and changed the date
setting in my regional options to display date as yyyy-MM-dd and it still
didnt seem to work. Can someone direct me? ThanksHi.
You have a language setting in the reports properties.
The default setting for language is "English (United States)". You can
change that either to what the user have choosen in IE by chosing "Default"
or what you want it to be. If you need yyyy-MM-dd you just change to your
country's setting.
You can override this setting for certain textboxes if you like. Textboxes
have their own language setting.
Kind regards
Maran
*********************
"MJT" wrote:
> I have a parm defined as DateTime in RS2005 (so you see the calendar control
> for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> display yyyy-mm-dd as it was when we had date defined as string rather than
> DateTime. I went into regional settings on my pc and changed the date
> setting in my regional options to display date as yyyy-MM-dd and it still
> didnt seem to work. Can someone direct me? Thanks|||Thank you Maran,
I had changed my regional settings in control panel prior to posting my
message, but I didnt realize that they wouldnt take effect until I closed out
of VS and re-entered. Which brings another question to mind. If we want the
date displayed as yyyy-MM-dd format can that be accomplished solely by
setting to "English(United States)"? Because when I noticed the problem
initially, I had my windows settings for date format as MM-dd-yyyy and my
language was English(United States) and that seemed to be why I saw the date
format as MM-dd-yyyy when I changed my parameter to DateTime format. I am
wondering if the users have their date format in windows set other than
MM-dd-yyyy if just making the language English(United States) will matter at
all now. Since that was the way my settings were originally. Is there a way
to enforce a date time format? I realize that each textbox has a format
parameter, but this textbox that referenced the date parameter had other code
in it which was being translated into multiple languages in a code block - so
I cant use the format property on the textbox itself - I would have to put it
in the code block somehow. Thanks again for your reply.
"Maran" wrote:
> Hi.
> You have a language setting in the reports properties.
> The default setting for language is "English (United States)". You can
> change that either to what the user have choosen in IE by chosing "Default"
> or what you want it to be. If you need yyyy-MM-dd you just change to your
> country's setting.
> You can override this setting for certain textboxes if you like. Textboxes
> have their own language setting.
> Kind regards
> Maran
> *********************
> "MJT" wrote:
> > I have a parm defined as DateTime in RS2005 (so you see the calendar control
> > for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> > display yyyy-mm-dd as it was when we had date defined as string rather than
> > DateTime. I went into regional settings on my pc and changed the date
> > setting in my regional options to display date as yyyy-MM-dd and it still
> > didnt seem to work. Can someone direct me? Thanks|||I handled this by adding the format to my custom code. Thanks.
"MJT" wrote:
> Thank you Maran,
> I had changed my regional settings in control panel prior to posting my
> message, but I didnt realize that they wouldnt take effect until I closed out
> of VS and re-entered. Which brings another question to mind. If we want the
> date displayed as yyyy-MM-dd format can that be accomplished solely by
> setting to "English(United States)"? Because when I noticed the problem
> initially, I had my windows settings for date format as MM-dd-yyyy and my
> language was English(United States) and that seemed to be why I saw the date
> format as MM-dd-yyyy when I changed my parameter to DateTime format. I am
> wondering if the users have their date format in windows set other than
> MM-dd-yyyy if just making the language English(United States) will matter at
> all now. Since that was the way my settings were originally. Is there a way
> to enforce a date time format? I realize that each textbox has a format
> parameter, but this textbox that referenced the date parameter had other code
> in it which was being translated into multiple languages in a code block - so
> I cant use the format property on the textbox itself - I would have to put it
> in the code block somehow. Thanks again for your reply.
> "Maran" wrote:
> > Hi.
> >
> > You have a language setting in the reports properties.
> >
> > The default setting for language is "English (United States)". You can
> > change that either to what the user have choosen in IE by chosing "Default"
> > or what you want it to be. If you need yyyy-MM-dd you just change to your
> > country's setting.
> >
> > You can override this setting for certain textboxes if you like. Textboxes
> > have their own language setting.
> >
> > Kind regards
> > Maran
> >
> > *********************
> >
> > "MJT" wrote:
> >
> > > I have a parm defined as DateTime in RS2005 (so you see the calendar control
> > > for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> > > display yyyy-mm-dd as it was when we had date defined as string rather than
> > > DateTime. I went into regional settings on my pc and changed the date
> > > setting in my regional options to display date as yyyy-MM-dd and it still
> > > didnt seem to work. Can someone direct me? Thanks

DateTime parameter format issue?

Hi All:

I create a report with a DateTime parameter, i would love to use DD/MM/YYYY this format, but it doesn't work . it works when i change to usa datetime format ,YYYY-MM-DD,

any helps are appreciated.

thanks

Nick

hi Nick

this is an example of what I use

this is put in as an expression - Layout view on a table

=Format(Fields!Date3.Value, "dd/MMM/yyyy")

hope this helps

|||

Hi, I had the same issue, this is a documented fix in SP1 for SQL 2005, using formatdatetime function you can fix this,

Andy

|||

It is really help.

i used "DD/mm/yyyy", it works

Thanks you very much jewelfire.

|||

Hi,

I have a report which has a datetime parameter,

when i choose the datetime from calendar control , then click view report button. if i choose 13/12/2006, it would give a error, because the report think it's 13 is month , how can i fix this issue. also i want keep dd/MM/yyyy format?

Thanks

Nick

DateTime parameter format issue?

Hi All:

I create a report with a DateTime parameter, i would love to use DD/MM/YYYY this format, but it doesn't work . it works when i change to usa datetime format ,YYYY-MM-DD,

any helps are appreciated.

thanks

Nick

hi Nick

this is an example of what I use

this is put in as an expression - Layout view on a table

=Format(Fields!Date3.Value, "dd/MMM/yyyy")

hope this helps

|||

Hi, I had the same issue, this is a documented fix in SP1 for SQL 2005, using formatdatetime function you can fix this,

Andy

|||

It is really help.

i used "DD/mm/yyyy", it works

Thanks you very much jewelfire.

|||

Hi,

I have a report which has a datetime parameter,

when i choose the datetime from calendar control , then click view report button. if i choose 13/12/2006, it would give a error, because the report think it's 13 is month , how can i fix this issue. also i want keep dd/MM/yyyy format?

Thanks

Nick

DateTime parameter format

Hi everybody

A quick question - is it possible to present DateTime report parameters with specified format. Let's say instead of '9/3/2005 12:00 AM' i'd like to show '9/3/2005' only. Plus maybe it's possible to plug in some sort of a popup calendar.

Thanks for response,
Konstantin

At least in RS 2000 if you define the parameter as string (instead of datetime) then it'll be shown in the format that you want.
And I don't know if the datepicker can be used in RS (maybe someone else could help us)|||One way of pluging in a popup calendar would be to create a front-end application to the report. You would use either the URL Builder functionality (fast) or the Web Service (slow) to run the report. The parameters could be created from any control (calendar, datagrid, etc...) There are some sample apps out there that do this (and some that run as Sharepoint web parts.)
An option to format dates is to use a Date Key (integer) and Date Name (formatted date) drop down list of dates. The report would have the date key passed in - the stored procedure would translate to the date selected.
Hope this helps,
Andrew.|||

I see
i guess second option would solve formatting issue.

Thanks,
Konstantin

Datetime Parameter Format

Hi,all
I have a datetime parameter,
I use calendar to select value,but I want to format it as "yyyy-MM"
Any suggest?Can I use expression,how to ?
Kevin ChuJust get the value in as datetime format and use
format(Fields!xxx.Value,"yyyy-MM")
--
Tom Stude
"Kevin" wrote:
> Hi,all
> I have a datetime parameter,
> I use calendar to select value,but I want to format it as "yyyy-MM"
> Any suggest?Can I use expression,how to ?
> Kevin Chu
>|||=?Utf-8?B?VG9t?= <membership@.stude.no> wrote in news:ADC758F7-F6A7-4F56-
8C5C-1BAD643ABF30@.microsoft.com:
I want to show "yyyy-MM" format in preview,not get the value
> Just get the value in as datetime format and use
> format(Fields!xxx.Value,"yyyy-MM")
>

Datetime Parameter Format

Hi,

I'm trying to test a stored procedure in VB and SQL Express 2005 - it has a smalldatetime field.

It works fine from the VB side using the data source Preview Data facility but when I try it in SQL Management Studio I get errors no matter what format I try!

I'm sure I'm missing something very simple - thanks in advance.

USE [Bookings]
GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[spAddReservation]
@.RES_TBL_ID = 1,
@.RES_TTL_ID = 1,
@.RES_Diner_Surname = N'Bloggs',
@.RES_Date = 28/05/2007 18:15:00,
@.RES_Meal_ID = 1,
@.RES_STA_ID = 1,
@.RES_OCC_ID = 1,
@.RES_STF_ID = 1

SELECT 'Return Value' = @.return_value

GO

The error this generates is
Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '/'.

I've tried with quotes (single or double) but then I get a convert error!
You need to include your date info quotes
You could set current datetime format by SET DATEFORMAT statement.
Also you could use CONVERT function:
@.Res_date = convert(datetime, '28/05/2007 18:15:00', 131)

|||

You won't get datetime conversion errors if you were to use the ISO standard date format = year/month/day,

e.g., '2007/05/28 18:15:00'

|||

See function CONVERT in BOL. If you use styles 112 (ISO) or 126 (ISO8601), then SQL Server will interprete correctly datetime constants no matter the settings for LANGUAGE and DATEFORMAT.

> @.RES_Date = 28/05/2007 18:15:00

@.RES_Date = '2007-05-28 18:15:00'

AMB

|||

I hope the error you got because of the missing quote..

When I check the dateformat it is DMY. (it may cause /throw another error if the system dateformat is different).

It is not bad idea to use the DATEFORMAT if the entier db uses the single format.

Try the following code..

Code Snippet

SET DATEFORMAT DMY;

USE [Bookings]

GO

DECLARE@.return_value int

EXEC@.return_value = [dbo].[spAddReservation]

@.RES_TBL_ID = 1,

@.RES_TTL_ID = 1,

@.RES_Diner_Surname = N'Bloggs',

@.RES_Date = '28/05/2007 18:15:00',

@.RES_Meal_ID = 1,

@.RES_STA_ID = 1,

@.RES_OCC_ID = 1,

@.RES_STF_ID = 1

SELECT'Return Value' = @.return_value

|||Thanks for this - worked a treat!

I had assumed the date format would be as per culture setting and was misled by the fact that that worked OK inside VB Express.

Datetime issue

In my DB, the date is in the following format : mm/dd/yyyy hh:mm:ss AM/PM
But wheni read the DB from the SqlDataReader, it returns the date in a different format : dd/mm/yyyy hh:mm:ss AM/PM
Why doesn't it return the date like it is in the DB?
For example, when i open the DB with Entreprise Manager : 5/25/2005 8:26:54 AM
But when i run the Debugger, it reads 25/05/2005 8:26:54 AM
Please help,
Thanks,
I think it may be your Windows regional setting and check you SQL Server configuration properties. Hope this helps.

datetime HOUR function format

I am using reporting services to make a matrix. The row value is the date portion of DateIn. The value is a count of transactions. The column type is the problem. It is the hour part of the timein value.

I got it from the database like this:

{fn HOUR(dbo.[Transaction].[TimeIn])} AS Hour

This works, but gives 24 hour time (and only the hour part, so it looks like 10, 11, 12, 13, 14, etc.)

I want it to look like 10:00 AM, 11:00 AM, 12:00 PM, 1:00, PM, etc.

I have read several books, checked online books, tried format functions... and I'm going nuts. This should be so simple- how do I format this so a human can read it? Thanks

If you need the database to do the conversion, then you can set the format code of the textbox to "t" and use the following expression.

=CDate(Fields!Hour.Value & ":00")

If you can use the raw date value from the database, then you can just set the format code of the textbox to "t". If you are grouping on only the hour, then you can still just get the raw date value from the database and use =Fields!TimeIn.Value.Hour as the group expression.

DateTime Formatting

Have a cell in a table that is being populated by a field in database that
contains the date and time in this format mm/dd/yyyy hh:mm AM. I need it to
be the otherway around ie. dd/mm/yyyy hh:mm AM/PM. But when i try and use
=Format(DateTime.Value, "dd/MM/yyyy hh:mm") is get an error saying that the
hh is not declared.
Any help on this would be greatly appreciated.Forget the format function. Right click the textbox and go to properties,
under format section click the custom radio button now you can define what
you want: "dd/MM/yyyy hh:mm tt" or whatever.
--
Message posted via http://www.sqlmonster.com

datetime format setting --> mm-dd-yyyy instead of dd-MM-yyyy in SQL Server 2005 / expre(is it

I’m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.}

My windows Regional and Language options – English (United Kingdom), Sort date format is dd-MM-yyyy.

When converting the date time in Sql server is using the mm-dd-yyyy format. But I’m supplying the dd-mm-yyyy format date time.

I tried number of things none of them worked for me

1. Tried changing the default language and get the date time format

- exec sp_configure 'default language', 2057
reconfigure

- did not work

EXEC sp_defaultlanguage 'my user name', 'British'

- did not work

(Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQLconversion-errors-or-blank-pages__592__.htm)

2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Setup
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

(Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891)

- did not work

3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format Did not work

4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work

(Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )

5. Uninstall and reinstall SQL server express several time and did the steps 1 – 4 where applicable – did not work….

If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates....

Please help me or point me in the direction in which I have to look for an answer.

Thank you very much….

Some SQL Server datetime is language dependent, there is a guide below you can use to change it.

http://www.karaszi.com/SQLServer/info_datetime.asp|||

Thanks Caddre for the post…

Your suggestion confirm me that the datetime format I’m using is Numeric one, which is LANGUAGE dependent… therefore my question of is it with sql server login language ? I guess valid…

……………………………………………..

The problem happening in my ASP.net application; I’m connecting to the Sql Server using connection-string : <add name="conn" connectionString="Data Source=hostname;Initial Catalog=dbname;User Id=myname;Password=password;" providerName="System.Data.SqlClient"/>

Here the default language of this ‘myname’ user is British English

In some installation of Sql Server when I supply datetime in “yyyy-mm-dd HH:mms.ms” format( E.g.: '2007-08-27 14:12:19.590') it work fine…

But in some other installation of Sql Server it throws the flowing exception

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

When I run the flowing command in both installations myname login, I’m getting the same result… as follows...

select SYSTEM_USER

- myname

select @.@.language

- British

I couldn’t find out what might be the problm between this two insallations?!!!!!

Any one have any suggestion? Please……………….

|||

You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application. I have covered that in the thread below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1837895&SiteID=1

|||

[You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application]

I’m aware that in order to avoid language dependence or language dependent format problems; I have to change to language neutral format, and my datetime format is not a language neutral format….

But the application is a legacy asp.net application and it was working fine in many client places…

In the app - No Stored procedures used for insert, update or select – any one to change it …All the database select, insert, and update are inline-sql-statements in the application which are .net assemblies (dlls)…. and I can not change the application - because I’m not the developer of the application….

The only option available for me is to find out a way to change the date time format of the login user so that it won’t throw exception after 12^th of each month…. That’s what I was trying to explain in my first post…

is there any solution for this?

I hope I’m clear on my description now…. sorry if I’m not clear on previous posts…

PS: - Please let me know if I’m not clear in this post ….or… if I should post this question to a different MSDN Forum…

Thank you…

|||

In the first place an application with inline SQL can get SQL injection and you have only two options either use the IsDate function or try using the British locale configuration in control panel. If that did not work you need to ALTER all the columns with Varchar as DateTime and make all the correction needed because client connection issue with bad code needs to be fixed.

http://msdn2.microsoft.com/en-us/library/aa176553(SQL.80).aspx

http://www.sql-server-helper.com/error-messages/msg-242.aspx

|||Hi all,
I am also having the SAME problem using MS ACCESS - supposedly an end user tool

I am a programmer of 30 years standing, so have some experience in building reliable inter application comms.

Now, I understand that the underlying technology is probably .NET

and that SQL is a bit vague on default date formats

HOWEVER

1) End users should not be exposed to this type of technologic problem
2) The MS Java driver gets it RIGHT FIRST TIME regardless of the regional and login settings in force
3) Similar problems have persisted for 15 or so years (Access, VB, Excel)

There is a work around - if your user will accept it - set Regional on the workstation to YYYY-MM-dd

MS - when can a more generic solution be delivered?
a) My customer is a MS solution provider
b) He does not want to migrate to YYY-MM-dd format since he DOES NOT KNOW WHAT THE IMPACT WILL BE ON HIS OTHER APPLICATIONS, and cannot afford the downtime in finding out
c) Should he change, there will be significant retraining of staff and losses due to incorrect data entry
d) All he sees is that he cant migrate from ACCESS / MDB to Access/SQL Server easily (both are MS product)
e) He has asked whether or not he should migrate to Java / Jasper / Mysql !

THE SOLUTION

MS - this should be in your court

1) You have 3 layers Access (or .net) , ODBC and SQL Svr
2) The first two are always on the client and thus can look at the same regional settings.
3) The .ODBC layer can interrogate the MS SQL server (or any other server for that matter) and establish what translations are required - or more simply establish its own convention e.g issue a SET DATE BRIT after establishing the connection. You could even invent a foolproof format of your own within proprietary extensions.
4) Workstation layer can look at regionalisation and ODBC setup options to determine connectivity
5) MS could even supply date format string options on the ODBC setup to define application and server preferred formats

RESULT

- ALL end user apps can now use SQL dates without mishap
- Bad applications that dont look at regionalisation can be catered for by configuring ODBC.
- User administrator can setup separate ODBC channesl and translation for all app variants

Everyone wins.|||

Thanks every one for the suggestions...

I have gone down the path of changing the windows Regional and Language options on the workstation to YYYY-MM-dd… (It worked….….)

And the problem of my head temporarily…

datetime format setting --> mm-dd-yyyy instead of dd-MM-yyyy in SQL Server 2005 / expre(is it

I’m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.}

My windows Regional and Language options – English (United Kingdom), Sort date format is dd-MM-yyyy.

When converting the date time in Sql server is using the mm-dd-yyyy format. But I’m supplying the dd-mm-yyyy format date time.

I tried number of things none of them worked for me

1. Tried changing the default language and get the date time format

- exec sp_configure 'default language', 2057
reconfigure

- did not work

EXEC sp_defaultlanguage 'my user name', 'British'

- did not work

(Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQLconversion-errors-or-blank-pages__592__.htm)

2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Setup
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

(Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891)

- did not work

3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format Did not work

4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work

(Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )

5. Uninstall and reinstall SQL server express several time and did the steps 1 – 4 where applicable – did not work….

If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates....

Please help me or point me in the direction in which I have to look for an answer.

Thank you very much….

Some SQL Server datetime is language dependent, there is a guide below you can use to change it.

http://www.karaszi.com/SQLServer/info_datetime.asp|||

Thanks Caddre for the post…

Your suggestion confirm me that the datetime format I’m using is Numeric one, which is LANGUAGE dependent… therefore my question of is it with sql server login language ? I guess valid…

……………………………………………..

The problem happening in my ASP.net application; I’m connecting to the Sql Server using connection-string : <add name="conn" connectionString="Data Source=hostname;Initial Catalog=dbname;User Id=myname;Password=password;" providerName="System.Data.SqlClient"/>

Here the default language of this ‘myname’ user is British English

In some installation of Sql Server when I supply datetime in “yyyy-mm-dd HH:mms.ms” format( E.g.: '2007-08-27 14:12:19.590') it work fine…

But in some other installation of Sql Server it throws the flowing exception

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

When I run the flowing command in both installations myname login, I’m getting the same result… as follows...

select SYSTEM_USER

- myname

select @.@.language

- British

I couldn’t find out what might be the problm between this two insallations?!!!!!

Any one have any suggestion? Please……………….

|||

You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application. I have covered that in the thread below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1837895&SiteID=1

|||

[You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application]

I’m aware that in order to avoid language dependence or language dependent format problems; I have to change to language neutral format, and my datetime format is not a language neutral format….

But the application is a legacy asp.net application and it was working fine in many client places…

In the app - No Stored procedures used for insert, update or select – any one to change it …All the database select, insert, and update are inline-sql-statements in the application which are .net assemblies (dlls)…. and I can not change the application - because I’m not the developer of the application….

The only option available for me is to find out a way to change the date time format of the login user so that it won’t throw exception after 12^th of each month…. That’s what I was trying to explain in my first post…

is there any solution for this?

I hope I’m clear on my description now…. sorry if I’m not clear on previous posts…

PS: - Please let me know if I’m not clear in this post ….or… if I should post this question to a different MSDN Forum…

Thank you…

|||

In the first place an application with inline SQL can get SQL injection and you have only two options either use the IsDate function or try using the British locale configuration in control panel. If that did not work you need to ALTER all the columns with Varchar as DateTime and make all the correction needed because client connection issue with bad code needs to be fixed.

http://msdn2.microsoft.com/en-us/library/aa176553(SQL.80).aspx

http://www.sql-server-helper.com/error-messages/msg-242.aspx

|||Hi all,
I am also having the SAME problem using MS ACCESS - supposedly an end user tool

I am a programmer of 30 years standing, so have some experience in building reliable inter application comms.

Now, I understand that the underlying technology is probably .NET

and that SQL is a bit vague on default date formats

HOWEVER

1) End users should not be exposed to this type of technologic problem
2) The MS Java driver gets it RIGHT FIRST TIME regardless of the regional and login settings in force
3) Similar problems have persisted for 15 or so years (Access, VB, Excel)

There is a work around - if your user will accept it - set Regional on the workstation to YYYY-MM-dd

MS - when can a more generic solution be delivered?
a) My customer is a MS solution provider
b) He does not want to migrate to YYY-MM-dd format since he DOES NOT KNOW WHAT THE IMPACT WILL BE ON HIS OTHER APPLICATIONS, and cannot afford the downtime in finding out
c) Should he change, there will be significant retraining of staff and losses due to incorrect data entry
d) All he sees is that he cant migrate from ACCESS / MDB to Access/SQL Server easily (both are MS product)
e) He has asked whether or not he should migrate to Java / Jasper / Mysql !

THE SOLUTION

MS - this should be in your court

1) You have 3 layers Access (or .net) , ODBC and SQL Svr
2) The first two are always on the client and thus can look at the same regional settings.
3) The .ODBC layer can interrogate the MS SQL server (or any other server for that matter) and establish what translations are required - or more simply establish its own convention e.g issue a SET DATE BRIT after establishing the connection. You could even invent a foolproof format of your own within proprietary extensions.
4) Workstation layer can look at regionalisation and ODBC setup options to determine connectivity
5) MS could even supply date format string options on the ODBC setup to define application and server preferred formats

RESULT

- ALL end user apps can now use SQL dates without mishap
- Bad applications that dont look at regionalisation can be catered for by configuring ODBC.
- User administrator can setup separate ODBC channesl and translation for all app variants

Everyone wins.|||

Thanks every one for the suggestions...

I have gone down the path of changing the windows Regional and Language options on the workstation to YYYY-MM-dd… (It worked….….)

And the problem of my head temporarily…

Datetime format regardless regional settings

Hi,

in tsql there is a common format for datetime (regardless regional settings)?

I use : 'mm/dd/yyyy' to access cols in database,

but someone says that the right one is: yyyy-mm-dd.

Any suggestion about that?

Thank a lot

I believe you want the ISO format -- 112; that is yymmdd. Can somebody double check this?

Dave

|||

yes dave.. 112 is the ansi unseperated date format.....

so best option is to get all ur date in this format: select convert(varchar, getdate(), 112)

|||

Hi,

I tried thid on nortwind DB (date are: dd/mm/yyyy)

select * from orders where orderdate < '19960704'

this does work, no data are retrieved,

but works for:

select * from orders where orderdate < '07/04/1996'

select * from orders where orderdate < '1996-07-04'

both select one row (if nortwind is the original one)

?

any suggestion?

All extract the same regardless the regional settings?

|||

Fast:

I tried all three queries against my copy of the northwind database. The lowest date that I have in the orders table is the date '07/04/1996'. For this reason, I do not get any rows returned with any of the select statements. However, when I change the "less than" operator to a "less than or equal to" operator I get the expected row. I am not getting the same results as you. I am afraid I can't help on this one. Sorry fo adding to confusion.

Dave

|||

hmm..works fine with me...the lowest date in northwind is..1996-07-04 00:00:00.000..

i get 0 rows for all the queries u mentiones...and 1 each when i use <= ..

neways...wat do u want to achieve exactally....see 112 is the ansi date format, which shud be used for date conversion and comparisons as its the standard....select convert(varchar(10), getdate(), 112) ...

yyyy-mm-dd +time is how sql server present it as..

|||

Sorry for having written too fast,

all three select retrieve 1 record if date is minor than 5 july 1996,

I have tried all queries with several regional settings and everything work.

If anyone has other suggestion, is welcome

Besides in internet there are this interestings articles:

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-delimit/format-dates-for-database-entry.html

and

http://www.karaszi.com/SQLServer/info_datetime.asp

Thank