how do i get date into dd-mm-yy format?

i have the following in an insert stored proc

CONVERT(CHAR(8),GETDATE(),10)

but this inserts 03-06-06.

how do i get it to 06-03-06?

Cheers,
Craig

Probably by setting the machine you're running on to run in a time zone where this format is standard ?|||

my sp is as follows:-

IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC insert for YES consent where a ConsentDate is supplied
BEGIN
INSERT INTO tblStudyServices
(SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
ReadmissSixMonths, Consent, ConsentDate, CreationDate)
VALUES
(@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs, @.intFourRegularDrugsNo, @.blnHRD,
@.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate, CONVERT(CHAR(8),GETDATE(),10))
END

@.dteActualConsentDate is passed in as 14-03-06

if i then look in the table it has

CosentDate = 14-03-06
CreationDate = 03-06-06

i have tried SET_DATEFORMAT dmy

but still no luck

|||Datetime values are not stored in any special format. There are special allowed format and rules for inserting datetime. For output, it is the client application that formats the value to something that is human-readable. See http://www.karaszi.com/SQLServer/info_datetime.asp for more information. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:3207581d-2ba8-4191-b788-7db50a967db0@.discussions.microsoft.com... >i have the following in an insert stored proc >
> CONVERT(CHAR(8),GETDATE(),10) >
> but this inserts 03-06-06. >
> how do i get it to 06-03-06? >
> Cheers,
> Craig >
>|||Did you see the reply I posted? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:acc61a6e-96e2-4412-9e4f-5ffdd7ab7b62@.discussions.microsoft.com...
> my sp is as follows:- >
> IF (@.blnConsent = 1 AND @.dteActualConsentDate <> NULL) --This is PC
> insert for YES consent where a ConsentDate is supplied
> BEGIN
> INSERT INTO tblStudyServices
> (SurveyID, PatientID, FourRegularDrugs, FourRegularDrugsNo, HRD,
> ReadmissSixMonths, Consent, ConsentDate, CreationDate)
> VALUES
> (@.intSurveyID, @.intPatientID, @.blnFourRegularDrugs,
> @.intFourRegularDrugsNo, @.blnHRD,
> @.blnReadmissSixMonths, @.blnConsent, @.dteActualConsentDate,
> CONVERT(CHAR(8),GETDATE(),10))
> END >
> @.dteActualConsentDate is passed in as 14-03-06 >
> if i then look in the table it has >
> CosentDate = 14-03-06
> CreationDate = 03-06-06 >
> i have tried SET_DATEFORMAT dmy >
> but still no luck >
>

How do I Filter by Current Date

I have used 'GETDATE()' but it appears to ignore the current date
because the data does nor have any time information in it. How do I get
filter the data just by date?
Regards
Colin
*** Sent via Developersdex http://www.examnotes.net ***Select *
From TableName
Where DateColumn = Cast(DateDiff(d, 0, Current_Timestamp) As DateTime)
Thomas
"Colin Spalding" <pupil@.alottolearn.com> wrote in message
news:eRPb0%23FZFHA.3164@.TK2MSFTNGP09.phx.gbl...
>I have used 'GETDATE()' but it appears to ignore the current date
> because the data does nor have any time information in it. How do I get
> filter the data just by date?
> Regards
> Colin
> *** Sent via Developersdex http://www.examnotes.net ***|||Colin Spalding wrote:
> I have used 'GETDATE()' but it appears to ignore the current date
> because the data does nor have any time information in it. How do I
> get filter the data just by date?
> Regards
> Colin
> *** Sent via Developersdex http://www.examnotes.net ***
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Colin Spalding wrote:
> I have used 'GETDATE()' but it appears to ignore the current date
> because the data does nor have any time information in it. How do I
> get filter the data just by date?
>
If you're storing time as well as date, then use greater than and less than:
WHERE datefield >= GETDATE() and datefield < dateadd(d,1,GETDATE())
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Bob,
My guess is that you typed in getdate(), but meant @.d, where
@.d is a date-only value that has the date required, and what you
ended up suggesting was an uncommon requirement: rows with [datefield]
some time in the 24 hour interval starting right this instant? If the
datefield values are all timestamped midnight, it will give the rows
from tomorrow, and otherwise, it may not be reproducible.
If "today" is needed, one solution is this:
where datefield >= dateadd(d,datediff(day,0,getdate()),0)
and datefield < dateadd(d,datediff(day,0,getdate()),1)
Steve Kass
Drew University
Bob Barrows [MVP] wrote:

>Colin Spalding wrote:
>
>If you're storing time as well as date, then use greater than and less than
:
>WHERE datefield >= GETDATE() and datefield < dateadd(d,1,GETDATE())
>Bob Barrows
>
>|||Steve Kass wrote:
> Bob,
>
> My guess is that you typed in getdate(), but meant @.d, where
> @.d is a date-only value that has the date required,
Yes. My bad
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Or alternatively (rather than the date calculations),
WHERE CONVERT(char(8),datefield,112) = CONVERT(char(8),GETDATE(),112)
as the 112 conversion style, will drop the time info and format the date
in an unambiguous format.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Bob Barrows [MVP] wrote:

>Steve Kass wrote:
>
>Yes. My bad
>

How do I figure out time and date at the same time?

Dear all,
I've got some problems with date and time (very silly, I know)
select * from crm_1 where log < datepart(yyyy,getdate())
and log < datepart(mm,getdate())
and log < datepart(dd,getdate())
The aforementioned query doesn't find this value:
2005-06-09 08:27:17.810
CREATE TABLE [dbo].[CRM_1] (
[ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
[log] [datetime] NOT NULL)
GO
Thanks a lot and regards,I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...ime
.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
> Dear all,
> I've got some problems with date and time (very silly, I know)
> select * from crm_1 where log < datepart(yyyy,getdate())
> and log < datepart(mm,getdate())
> and log < datepart(dd,getdate())
> The aforementioned query doesn't find this value:
> 2005-06-09 08:27:17.810
> CREATE TABLE [dbo].[CRM_1] (
> [ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
> [log] [datetime] NOT NULL)
> GO
> Thanks a lot and regards,|||Try this:
select * from crm_1 where datepart(yyyy,log) < datepart(yyyy,getdate())
and datepart(mm,log) < datepart(mm,getdate())
and datepart(dd,log) < datepart(dd,getdate())
Or better yet, do some more reading as Tibor suggests.
ML|||It has been very useful. thanks a milion
"Tibor Karaszi" wrote:

> I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...i
me.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
>