Converting a date in the form of a string to a datetime format

Hi,

I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:

'01/'&Month(OrderDate)&'/'&Year(OrderDate)

Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.

There is a CONVERT function, but I'm new to all this and can't seem to get it to work.

Any help would be gratefully received!

Cheers!

Keith

you can group by without having to transform the date and that is

done like the example below

use northwind
select year(orderdate) as year, month(orderdate) as month , count(*)as ordercount from orders
group by year(orderdate),month(orderdate)
order by 1,2

|||

Try,

select'01/'+convert(varchar(2),month(OrderDate))+'/06'from Table

|||the year shouldn't be static... just add convert(varchar(2), year(OrderDate)) in place of 06|||

Cheers!

I'll give these a try.

Is the plus symbol '+' used in place of '&' to concatenate strings together in transact SQL then?

Also, I looked up varchar(2) in my ASCII list and it denotes 'start of text'. Why is this necessary out of interest?

Many thanks indeed!

Keith

|||

Ok, I've given the following a go:

SELECT '01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate)) AS 'OrderMonth'

It runs, but this is the result:

01/12/*

For some reason, the year is represented by a *. Any ideas why this may be?

Also, the expression as it is above still brings back a date (albeit with a * for the year) in a string format. Ideally, I want to convert all of that to a date format so it behaves like a date. To that end, I then took the above a bit further and attempted this:

SELECT CONVERT(Datetime,'01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate))) AS 'OrderMonth'

This basically encapsulates the exact expression above within a 'top level' CONVERT statement to convert it to datetime. I get the following run time error message though:

'Syntax error converting datetime from character string'. It's probably due to the *. To someone on here, it's probably obvious what I'm doing wrong.

Any ideas?

Many thanks!!!

|||

What about this:

SELECTDATEADD(mm,DATEDIFF(mm,0,orderdate), 0)as firstdayofOrdersDates ,count(*)as ordercount from orders

groupbyDATEADD(mm,DATEDIFF(mm,0,orderdate), 0)

|||

Hi limno,

Whilst your illustration doesn't quite bring back what I need, it has pointed the way to exactly what I need! See below what I have used:

SELECT DateAdd(Day,-Day(OrderDate)+1,OrderDate) AS 'OrderMonth'

Essentially what this is doing is dedecting the number of days into the month for a particular order date to get back to the last day of the previous month. I then add on 1 to give me the first day of the current month. Using this in the GROUP BY statement then nicely groups all my orders by month!

So really, I didn't need to do any converting of string expressions after all. I can't believe I didn't think of this before. It must have been staring me in the face!

Cheers for your help!

sqlsql