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 types and getdate() comparison

SQL 2000. Let's say column MyDate is a datetime type. Is this
comparison syntax OK as is?
... where MyDate <= getdate()
Or is some formatting of the column value and/or of the function's
return value required for the comparison to work?
Thanks
LiamComparison operators (<,>,=, <>, >=, <= ) are allowed between two values wit
h
a datatype of datetime. Your expression is fine.
However, if you want to do things like add or subtract datetime values, you
will need to use the date and time functions in SQL Server.
"Liam" wrote:

> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> .... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam
>|||depends on what you need
but don't convert the column - you'll lose any sargability if it's indexed.
i tend not to try to rely on date data having being inserted with a time
of midnight, so i convert the variable and perform range queries
if you need mydate <= just the date: then do
MyDate < tomorrow at midnight
e.g.
where MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need MyDate for just today
where MyDate >= dateadd(day, datediff(day, 0, getdate()), 0)
and MyDate < dateadd(day, datediff(day, 0, getdate()), 0)+1
or if you need mydate <= current date and time, then simply using
getdate() is appropriate.
Liam wrote:
> SQL 2000. Let's say column MyDate is a datetime type. Is this
> comparison syntax OK as is?
> ... where MyDate <= getdate()
> Or is some formatting of the column value and/or of the function's
> return value required for the comparison to work?
> Thanks
> Liam

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

DateDiff + Where am I going wrong?

Hi,

I'm creating a sample report in Visual Studio 2005 to be used in SSRS 2005.

I'm attempting to do some conditional formatting like so:

=IIF(DateDiff("Day", Fields!Jobseeker.Last Interview.Value, NOW()) > 14, "Red", "Transparent"

When ever I try and preview the report, VS throws a wobblie and closes down. The format of the Last Interview column is dd/mm/yyyy.

Any suggestions

Thanks, Steve

Hi, Steve,

Use VB parameters to DateDiff - ... try 'd' instead of "Day".

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp

Also verify that the "Last Interview" field name has an underscore "Last_Interview" - that should have happened automatically when you created a dataset from the query.

_

This posting is provided "AS IS" with no warranties, and confers no rights.

Date/Time formatting

Hi all, I'm hoping this is an easy solution . . .
How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
Am I missing something? I am fairly new to sql server . . .
I would use datepart, but I cannot find a way to display the time like
I have shown.
Your help is appreciated.
Thank you and have a great night,
Ryan<ryan.mclean@.gmail.com> wrote in message
news:1130715275.732555.138960@.g43g2000cwa.googlegroups.com...
> Hi all, I'm hoping this is an easy solution . . .
> How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
> Am I missing something? I am fairly new to sql server . . .
> I would use datepart, but I cannot find a way to display the time like
> I have shown.
> Your help is appreciated.
> Thank you and have a great night,
> Ryan
>
The way your dates and times are displayed is controlled by your client
application, not by SQL Server. Format the value client-side.
If you must control the format from the server then you'll have to return a
string instead of a DATETIME. Take a look at the CONVERT function in Books
Online. CONVERT allows you to convert DATETIME to a string in any one of
various formats.
David Portas
SQL Server MVP
--|||<ryan.mclean@.gmail.com> wrote in message
news:1130715275.732555.138960@.g43g2000cwa.googlegroups.com...
> Hi all, I'm hoping this is an easy solution . . .
> How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
> Am I missing something? I am fairly new to sql server . . .
> I would use datepart, but I cannot find a way to display the time like
> I have shown.
> Your help is appreciated.
> Thank you and have a great night,
> Ryan
>
The way your dates and times are displayed is controlled by your client
application, not by SQL Server. Format the value client-side.
If you must control the format from the server then you'll have to return a
string instead of a DATETIME. Take a look at the CONVERT function in Books
Online. CONVERT allows you to convert DATETIME to a string in any one of
various formats.
David Portas
SQL Server MVP
--|||Hi David,
First, thanks for posting a reply . . .
I am displaying the data in an asp.net datagrid, so it is actually more
efficient if I format the data on the database server in my sql
statement.
I have looked at the convert function, and I don't see where I can
format the data how I have specified. In ocacle, I would use to_date
and it would be done.
Thanks for trying, but I need a little more guidance then suggesting a
function.
Ryan|||I'm surprised that you think it's more efficient to do formatting in
the database than in ADO. Usually formatting is done client side
precisely to avoid putting that overhead on the database. ADO provides
date formatting through the ToString method for that purpose.
Aside from efficiency, doing it at the client has the advantage of
being user-configurable. Apparently you want to force all your users to
conform to your preference on how they should view dates. Also, if you
return a formatted string in the form you suggested then the user won't
be able to sort on it.
To do it in the database would look something like this:
SELECT CONVERT(CHAR(10),CURRENT_TIMESTAMP,101)+
'
'+RIGHT(CONVERT(VARCHAR,CURRENT_TIMESTAM
P,0),8)
David Portas
SQL Server MVP
--|||>> I have looked at the convert function, and I don't see where I can format
SELECT CONVERT( VARCHAR, GETDATE(), 101 ) +
RIGHT( CONVERT( VARCHAR, GETDATE(), 100 ), 8 )
Anith|||Hi again,
David, that is a good point and it would probably be more applicable if
it were a distributed app (it's just an admin tool that we will use).
I just don't see the advantage of pulling back a data set, then looping
through every record and messing with each item individually when I can
have the database engine do all the work, then I just have to display
it.
Anyway, always a learning process. I really appreciate your posts.
Also, a thank you to Anith.
Thanks and have a great day,
Ryan