How to Format DATE 2

[sciclunam]

Hi,

I need to query a date field in my table. I wanted to convert it to mm/dd/yyyy and thus I used the CONVERT(datetime,mydatefield,101) but it is giving the same default result in the SQL Query analyzer.

Also in some cases I need to extract the year from the date field or the month etc... What can I use to format date fields in SQL.

In oracle it was quite simple but can`t figure it out in SQL.

Thanks for your help.

Mario

 

[dinzana]

Hello Mario,

Try the following:

select CONVERT ( varchar(10) , getdate(), 101 )

I hope that this helps,

-dinzana

 

[dinzana]

Here are some ways to extract the month, day and year values from the converted string.


select substring((CONVERT ( varchar(10) , getdate(), 101 )),1,2)-- for month

select substring((CONVERT ( varchar(10) , getdate(), 101 )),4,2)-- for day

select substring((CONVERT ( varchar(10) , getdate(), 101 )),7,4)-- for year


Hope this helps,

-dinzana

 

[SQLBill]

Mario,

What went wrong is the use of DATETIME in your CONVERT statement. You converted it back to the default datetime format.

As the other posters showed, you need to use VARCHAR or CHAR.

Also, check out the command DATEPART in the Books OnLine (BOL).

-SQLBill

 

[sciclunam]

Thanks guys. You were all extremely helpful.

select substring((CONVERT ( varchar(10) , getdate(), 101 )),1,2) gave me a result of 5.

Is there a way to extract 'MAY' (ie: the month) instead?

Thanks.

 

[sciclunam]

me again...

Is there a simple way to choose a record out of many similar ones by querying for the 'maximum' date ie: the more recent records only.

 

[SQLBill]

Check out DATEPART and DATENAME in the Books OnLine.

DATEPART returns a number for whatever part you want.
DATENAME converts a number into the part you want.

SELECT DATEPART(month, getdate())
returns 9

SELECT DATENAME(month,(DATEPART(month,getdate()))
returns SEPTEMBER

SO,

select DATENAME(month,(substring((CONVERT ( varchar(10) , getdate(), 101 )),1,2)))

Should give you what you want.

-SQLBill

 

[sciclunam]

Now I am using the convert command to select data between two particular dates. However all dates are returned...ignoring my Where condition. This is the code:

SELECT
process.Process_Name,
CONVERT(varchar(10), assessment.assmt_date, 103) AS Assessment_Date,
assessment.userinput AS Inputted_By,
assessment.fin_total,
assessment.rep_total,
assessment.hum_total,
assessment.Inherent_risk,
HRD_DEPARTMENTS_LIST.DEPARTMENT AS Office

FROM
assessment

INNER JOIN
process

ON assessment.process_rec_ref = process.rec_ref

INNER JOIN
HRD_DEPARTMENTS_LIST ON
assessment.hrd_departments_list_rec_ref = HRD_DEPARTMENTS_LIST.REC_REF

WHERE
(CONVERT(varchar(10), assessment.assmt_date, 103) <= '18/12/2003') AND (CONVERT(varchar(10), assessment.assmt_date, 103) >= '01/01/2003')

--------------------
The WHERE PART IS BEING INGNORED AND I AM GETTING RECORDS DATED IN 2001 Also.

ANy help is greatly appreciated.

Mario

 

[sciclunam]

Me again...

I think I figured it out...

WHERE assessment.assmt_date>= convert(datetime,'01/06/2003',103)

Anyone knows a function which chooses the records with the maximum date; ie: most recent out of duplicate records?

 

[Kolt]

Anyone knows a function which chooses the records with the maximum date; ie: most recent out of duplicate records?
- ....where date = ( select max(date) from table )

 

[sciclunam]

thanks but that gives only records having the maximum date of all the table. what I mean is this

let us say a table has these records:

Record a - 01/01/2003
Record b - 01/01/2003
Record b - 02/01/2003
Record c - 01/03/2004

I want as results:
Record a - 01/01/2003
Record b - 02/01/2003
Record c - 01/03/2004

Record b - 01/01/2003 has been ignored as there is another Record b with a 'bigger' date.

 

[Kolt]

use DISTINCT to elaminate duplicates:
select distinct date from table order by date

 

[JamesLean]

If these are the only two columns you want, then use:

SELECT col1, MAX(col2)
FROM tbl
GROUP BY col1

If you have other columns you need to return as well then use:

SELECT col1, col2, col3, col4
FROM tbl t1
WHERE col2 = (
  SELECT MAX(col2) FROM tbl
  WHERE col1 = t1.col1
)

--James

 

[sciclunam]

Dear James,

Thanks. I tried as you told me but still I am getting the duplicate records havind different dates. I should only get the highest date, shouldn`t I? This is the code

SELECT
a.rec_ref,
process.Process_Name,
CONVERT(varchar(10), a.assmt_date, 103) AS Assessment_Date,
a.userinput AS Inputted_By,
a.fin_total,
a.rep_total,
a.hum_total,
a.Inherent_risk,
HRD_DEPARTMENTS_LIST.DEPARTMENT AS Office
FROM assessment a
INNER JOIN process
ON a.process_rec_ref = process.rec_ref
INNER JOIN
HRD_DEPARTMENTS_LIST ON a.hrd_departments_list_rec_ref = HRD_DEPARTMENTS_LIST.REC_REF
WHERE a.assmt_date =(select max(assmt_date) from assessment where assessment.rec_ref = a.rec_ref)

Whilst thanking you once again, I am sorry I might be asking 'silly' questions.