Problem of Selecting Data With DateTime datetype 1

[cmlimnet]

Dear All,

I have create a table in MSSQL 7.0 server like the below.

Table Name : MyUser
Field 1 : UserId varchar (20)
Filed 2 : BirthDate DateTime

Sample Data From SQL Server Enterprise Manager Console:

userid birthdate
-------------------- ---------------------------
abc 7/20/2002 12:29:06 PM
xyz 7/20/2002 12:29:28 PM

Sample Data From SQL Server Query Manager:

userid birthdate
-------------------- ---------------------------
abc 2002-07-20 12:29:05.623
xyz 2002-07-20 12:29:27.857

I wrote a sql statement in VB appliacation like this

Select * from myuser where birthdate = '7/20/2002'.

But I cant get any record. How do I select only using partial date or partial time only? According to MSDN, I should use "Like", but I still cant figure it out.

Anyone know how to handle date time in VB and MSSQL server ?

Best Regard,
the rockyu

 

[DougP]

try this
Select * from myuser where birthdate Like '7/*/2002'.

Or

Select * from myuser where birthdate Like '7/%/2002'.
DougP, MCP

 

[skicamel]

Try this:

Select * from myuser where convert(datetime,convert(varchar,birthdate,112)) = '7/20/2002'

 

[cmlimnet]

Hi Guys,
Thank you very much for helping me!!!

I have try DougP suggestion but there was no record selected.

Then I have used skicamel way.Surprisingly, it is working well. But I dont understand how it is work???
But,at least I can solve my problems.

Thank you.
Best Regards,
the rockyu

 

[skicamel]

When you say birthdate = '7/20/2002', SQL interprets this as '7/20/2002 00:00:00', or midnight, matching none of your table's data from throughout the day.

What I did first was to tell SQL to convert your data to a varchar.

convert(varchar,birthdate,112)

which strips off the time on your data. Run this to see the difference:

select version = 'datetime', date = getdate()
union all
Select version = 'varchar', date = convert(varchar,getdate(),112)

After stripping off the time, I converted the varchar back to a datetime data type with
convert(datetime, convert(varchar,birthdate,112) )

The 112 formats the output of the date. Check out 'CAST and CONVERT' in SQL Books Online for more on that. Some good things to see there. ;-)