RE: query to get user names from varchar date col

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

RE: query to get user names from varchar date col

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-08-31 : 21:28:40

Hi,

I have a old table Orders with 2 columns

userName(nvarchar(100),null)
dateCreated (nvarchar(50),null)

Some of the data values for the dateCreated column are:
========================================================
6/3/2008 6:13:45 PM
12/07/1990

select userName
from Orders
where dateCreated is in the last 2 years

How would I do it given the varied data in this column and also it is of type varchar?

Any suggestions ideas inputs would be greatly appreciated.

Thanks,
SA

SA

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-31 : 22:34:53

[code]SELECT userName
FROM Orders
WHERE dateCreated >= 'Your date'[/code]
something like this?

Hope can help...but advise to wait pros with confirmation...

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-01 : 01:01:05

Hi,

How would I say in the last 2 year with the mixed up data set for dateCreated

SA

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-01 : 01:12:43

SELECT userName
FROM Orders
WHERE dateCreated like '%2009%'

SELECT userName
FROM Orders
WHERE dateCreated like '%2008%'

Would this pull out the right data set for dateCreated in the last 2 years esp with the mixed up dataset and dateCreated is varchar. Also is there a way to

1) Combine this two queries and
2) Can we output the result set into a text file so that I can use it for further manipulation.

Thanks and looking forward to further inputs!!

SA

matty
Posting Yak Master

161 Posts

Posted - 2009-09-01 : 01:40:53

DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate = '01/01/2008'
SET @EndDate = '01/01/2010'

SELECT userName
FROM Orders
WHERE dateCreated >= @StartDate AND dateCreated < @EndDate

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-09-01 : 02:08:17

I get the following error - (please note that the dateCreated is nvarchar)

Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type datetime.

SA

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-01 : 02:43:48

SELECT userName
FROM Orders
WHERE dateCreated like '%2009%' OR dateCreated like '%2008%'

Rahul Shinde

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 05:26:47

1 Always use proper DATEITME datatype
2
DECLARE @StartDate DATETIME,@EndDate DATETIME
SET @StartDate = '01/01/2008'
SET @EndDate = '01/01/2010'

SELECT userName
FROM Orders
WHERE convert(datetime,dateCreated,103) >= @StartDate AND convert(datetime,dateCreated,103) < @EndDate

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources