Date format question??? 2

[bj1]

Hi there,

I have a the date in the format:

20/02/03 02:20:16

Is there a way i can convert this to see what day of the week it is?

i.e
i am wanting to do a report that is based on Mondays only.

Is this possible?

Thanks for your help.

B

 

[Bobalooey]

The following will show you the day of week for the current date:

select datepart(dw,getdate()) as dayofweek

Depending on what your SET DATEFIRST setting is, and assuming Monday has a dw value of 2, your query could simply look like this:

select * from Table 
where datepart(dw,Table.DateField) = 2

 

[bj1]

the date i am working with is varchar data type not date data type.
So how do i convert this before woking out which day of the week a list of dates are?

Thanks,
B

 

[bj1]

i am trying to convert to date data type but keep getting the error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

this is what i am trying to do:

convert(varchar(17), convert(datetime, starttime), 121) AS 'Date'

unsure of what to do...

 

[IT2003]

Since starttime is varchar, rearrange in mm/dd/yy format and convert.

 

[bj1]

no matter what format it is arranged in it still gives me the error of out-of-range datetime value.

I also still want the time part of it as well.

I am trying to find out for example, how many calls are made on mondays between 10am and 2pm... and then i want to be able to do this with other days as well.

Any suggestions?

 

[IT2003]

declare @dt varchar(25)
set @dt = '20/02/03 02:20:16'
select convert(datetime, substring(@dt,4,2) +'/'+left(@dt,2)+ '/' +substring(@dt,7,2) + right(@dt,9)) as date


the result is
date
------------------------------------------------------
2020-03-02 02:20:16.000

(1 row(s) affected)

 

[bj1]

sorry for my ignorance... but the query above seems to be for only one date.

I have a database with thousands of calls logged - so have many dates i need to convert.

I just want to know how to find out how i can figure out what day of the week the dates in my database are.

but before i can do that i need to convert the date field from varchar to date datatype...

 

[Bobalooey]

bj,

Can you select some rows showing the varchar dates you speak of in SQL Query Analyzer? Then cut & paste some in a reply to this message and I've no doubt we can get you moving forward.

 

[bj1]

Thanks Bobalooey,

Here are the varchar dates: (randomly selected)

09/10/02 13:57:44
21/01/03 12:03:32
30/04/03 12:06:31
20/05/03 14:17:55

Is there a way i can run a query that will create a column that will tell me what day of the week these dates are?

I still need the time to be included as i need to know when the busiest times of day are.

Thanks for your help,
BJ

 

[Bobalooey]

I can see that your dates are dd/mm/yy format, that changes some things. To simulate your problem, try the following query, it should throw the same error you've been experiencing.

declare @sourcedate varchar(17)
select @sourcedate = '21/01/03 12:03:32'
select convert(datetime,@sourcedate) as sourcedate

Now let's correct it to get the desired result

declare @sourcedate varchar(17)
select @sourcedate = '21/01/03 12:03:32'
select 
substring(@sourcedate,4,3) + left(@sourcedate,3) + substring(@sourcedate,7,11) as sourcedate,
datepart(dw,convert(datetime,substring(@sourcedate,4,3) + left(@sourcedate,3) + substring(@sourcedate,7,11))) as dayofweek

You should get the results:

sourcedate        dayofweek   
----------------- ----------- 
01/21/03 12:03:32 3

This should now do what you want in a query, just substitute your varchar datetime field (assume its name is PurchaseDate) for the @sourcedate and you should get the desired result. Like this:

select 
substring(PurchaseDate,4,3) + left(PurchaseDate,3) + substring(PurchaseDate,7,11) as PurchaseDate,
datepart(dw,convert(datetime,substring(PurchaseDate,4,3) + left(PurchaseDate,3) + substring(PurchaseDate,7,11))) as dayofweek
from Orders (nolock)

 

[IT2003]

replace @dt with the column nameselect convert(datetime, substring(dt,4,2) +'/'+left(dt,2)+ '/' +substring(dt,7,2) + right(dt,9)) as date, datepart(dw,convert(datetime, substring(dt,4,2) +'/'+left(dt,2)+ '/' +substring(dt,7,2) + right(dt,9))) as dw from dttbl

result is

date dw
------------------------------------------------------ -----------
2002-10-09 13:57:44.000 4
2003-01-21 12:03:32.000 3
2003-04-30 12:06:31.000 4
2003-05-20 12:57:25.000 3
2003-05-24 16:50:00.000 7

(5 row(s) affected)

 

[bj1]

Thank you so much...
This is exactly what i wanted...
Thank you for being so patient with me.

BJ