Converting varchar to smalldatetime i think?

[hpadwal]

hello all,

i have a coloumn in my database which is:

user_dob varchar 100

and i have created a stored produre to select email address where users are between certain dates.

However it is now working can you help?

CREATE PROCEDURE GetByAgeEmail 

AS

SELECT user_email  FROM tbl_Users WHERE  user_html_info = 1 and (user_dob  BETWEEN convert (smalldatetime, user_dob, 103) =  '8/3/1987' AND '7/3/1991')
GO

Wha am i doing wrong? a lot i think!

Regards

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[jbenson001]

What does the data in the column look like?
Jim

 

[bborissov]

How the values are stored in user_dob?

Borislav Borissov

 

[hpadwal]

arr,

some are:

4/5/1963

then some are:

07/10/1986

and also

10/26/1988


:S i didnt design the database by the way!


_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[vongrunt]

How 'bout

SELECT user_email  
FROM tbl_Users 
WHERE  user_html_info = 1 
	and convert (smalldatetime, user_dob, 101) BETWEEN  'startdatehere' AND 'enddatehere'

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[hpadwal]

ouch, in that case i need to first convert With century (yyyy), 101 dates (american style) in to 103!

Then i can go ahead with my query!

can you help with this too?

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[SQLDenis]

this will get you started

declare @v varchar(50)
select @v= '4/5/1963'

SELECT convert(smalldatetime,PARSENAME(replace(@v,'/','.'), 1) +
right('00' + PARSENAME(replace(@v,'/','.'), 2),2) +
right('00' + PARSENAME(replace(@v,'/','.'), 3),2) )

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Easy with string functions

user_dob is in US format and you are using UK, right?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLSister]

First thing is to check and see if you have any dates that won't convert to a datetime value.

Select * from table1 where isdate(field1) = 0

These records will need to be fixed before the other query can work.

Next:

SELECT user_email  FROM tbl_Users WHERE  user_html_info = 1 and cast(user_dob as datetime) BETWEEN  '8/3/1987' AND '7/3/1991'

Note I used datetime rather than small datetime becasue I cannot check with isdate for values that are in datetime but not in small datetime so I coudl still fail the select if I used small datetime.

Third and most important! If you do not already have this, write a trigger on the table to check for valid datetime values in the data entry process and write a process on the user interface which will reject a non-valid date. This will ensure that the data will always be covertable. Personally I would change the datatype but there may be some reason why you cannot.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[vongrunt]

Trigger... Wouldn't simple CHECK constraint be enough?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLSister]

yeah vongrunt you are probably right. And better to do it that way.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[hpadwal]

yes i have both US and UK formats in the user_dob, if i convert to smalldatetime 103 will the US dates be converted correctly?



_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[SQLDenis]

Also forget about that parsename nonsense I posted before all 3 of your dates can be converted to datetime
select convert(datetime,'4/5/1963'), convert(datetime,'07/10/1986'), convert(datetime,'10/26/1988')


Why don't you just make it a datetime field after you check with isdate?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> yes i have both US and UK formats in the user_dob

This is [!]ambiguous[/!] data - there is no way to determine whether '08/03/2005' represents March 8th or August 3rd. Unless you have some other column/information that says 'US' or 'UK' or something.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[hpadwal]

i have around 4000 dates to convert, i was hoping to send an email to users between a specific date. rather than actually changing data.

In the past i have used the following on another database would like the same sort of method. here it is if it helps:

CREATE PROCEDURE getAgebydate 
	@PassDay int,
	@PassMonth int
AS

DECLARE @DT DATEtime
SET @DT = getDate()

DECLARE @ThisDay int
SET @ThisDay = (RIGHT(CAST(100+DATEPART(DD,@DT) AS CHAR(3)),2) )

DECLARE @ThisMonth int
SET @ThisMonth = (RIGHT(CAST(100+DATEPART(MM,@DT) AS CHAR(3)),2) )

SELECT * FROM tbl_User WHERE DATEPART(DD,DOB) = @PassDay AND DATEPART(MM,DOB) = @PassMonth and ReceiveInfo = 1
GO

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

- Doh

http://www.my-portfolio.me.uk

 

[SQLSister]

YOu can certainly do something like that but as vongrunt says how will you tell which are us dates and which are UK dates?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief