DateTime Format Problem: Setting to 16/Mar/2006 8:50:00 AM

my Current DateTime Format is 01/08/2006 9:15:00 AM

i want to set it to 01/Aug/2006 9:15:00 AM

what parameter will be pass in "SET DATEFORMAT"?

plz help and give me a chance of thanks.

You can't.

Shouldn't be trying to use SQL Server to do presentation formating anyhow.

|||

thanksMotley !

Idon't want to use SQL Server to do presentation formating . I want to fix this format for storage of DateTime data any time. I mean that any time any one tries to insert the DateTime data in my Database, that data should be store in my required format.

|||

Datetimes don't have a "format" (Ok, technically they do, but not a string format). They are stored as the number the days (and fractions there of) since the epoch. It does not store the months, years, hours, minutes, or seconds, let alone store them in any user-specified "format".

The fact that query analyzer/management studio/visual studio presents that value to you in a particular string format is for your benefit as a matter of presentation (AKA presentation formatting). SQL Server does have some capability in being able to convert to/from a datetime and varchar, but it's fairly rudimentary since you really shouldn't be doing presentation formatting in the database.

It's similiar to wanting to store the number of dollars of something into a table and asking how to get an integer field to accept the format "$5,000". It can't. That's not the databases job, that's for the presentation layer of your application to do (Convert $5,000 to 5000).

|||

again Thanks Lot Motley!

I have 2 different machines. Both having the SQL Server 2000 Professional Editon and OS = Windows XP.

One of the machine(M1) displays datetime like 16/Mar/2006 8:50:00 AM

while another machine(M2) has format 16/03/2006 8:50:00 AM

how to set same format for M2 as M1.

|||

As far as how the system displays it, it is probably a Window's setting. Have a look in Regional and Language Options from Control Panel - there is one for Long Formats, and one for Short. I don't think that how it is displayed is controlled by SQL Server, since as the other chap mentioned, SQL Server doesn't store dates in any kind of "format".

Otherwise, you can specify a format from SQL Server, but it will convert it from the datetime object to a varchar. You do this using the CONVERT function:

CONVERT

(varchar(12),p.date_effective,106)

The final parameter, 106 in this case, is the datetime format you want to use. A complete list of them is available at this site:

http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Hope this helps!

|||As dominic mentioned, whatever application you are using to view the data is probably getting your preferred date format from the system settings of the client machine. You can set that in the control panel->Regional and Language Options->Customize->Date(Tab)->Short Date Format->dd-MMM-yyyy|||

yeah this was the solution.

Thanks Motley.

datepart

I am trying to use datepart to determine what row in a table a users
hiredate is closest to current system date
example
JOE was hired in Mar 01 2000
I need to get his payrate based off months experience
<12 months
<24 months
<60 months
<120 months
<200 months
Thanks
mike
something like below.
select hiredate, monthsrow from payee, payrategroup where
hiredate,
getdate(),
ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
+
ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
monthsrowHi
CREATE TABLE #Test
(
empl INT NOT NULL PRIMARY KEY,
hiredate DATETIME NOT NULL
)
INSERT INTO #Test VALUES (1,'20060101')
INSERT INTO #Test VALUES (2,'20060101')
INSERT INTO #Test VALUES (3,'20060409')
INSERT INTO #Test VALUES (4,'20060110')
INSERT INTO #Test VALUES (5,'20060112')
INSERT INTO #Test VALUES (6,'20060120')
INSERT INTO #Test VALUES (7,'20060108')
INSERT INTO #Test VALUES (8,'20060103')
DECLARE @.dt DATETIME
SET @.dt ='20060115' --desired date
SELECT TOP 1 WITH TIES *
FROM #Test WHERE hiredate>'20050101' AND hiredate < DATEADD(day,1,@.dt)
ORDER BY hiredate DESC
<ciojr@.yahoo.com> wrote in message
news:1144550863.151230.197030@.t31g2000cwb.googlegroups.com...
>I am trying to use datepart to determine what row in a table a users
> hiredate is closest to current system date
> example
> JOE was hired in Mar 01 2000
> I need to get his payrate based off months experience
> <12 months
> <24 months
> <60 months
> <120 months
> <200 months
> Thanks
> mike
> something like below.
> select hiredate, monthsrow from payee, payrategroup where
> hiredate,
> getdate(),
> ltrim(datediff(month, experience_date, getdate()) / 12) + '.'
> +
> ltrim(datediff(month, experience_date, getdate()) % 12) as months <=
> monthsrow
>|||Hi Mike,
Can you give the ddls and the expected output. The question seems to be
a bit confusing.|||not what I am looking for.

Dateadd problem

Try this:

print dateadd(y,+4, '29 feb 2008')

Funny how the 1 day that is used to go from 29 jan to 1 mar stack if you add multible years to a day.

+4 year should put it back on 29 feb 2012, +3 years should put it on 1 mar 2011.

Well, mabye it has been posted before, I just found it interesting.

That is because you are using y not yy. Look in the BOL for the parameters. dy/y is dayofyear not year.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de