How can I convert a SQL date/time to just date?

I think this is incorrect... its supposed to be:

convert(<datatype to conver to>, <field>, <style>)

so.. if I have a field called transactiondate then my sql is:
select convert(char(10), transactiondate, 101) as Dates from mytable

or if you had birthdays in a table you wanted to query...

select name, age from birthdays where convert(char(10), transactiondate, 101) = '01/04/1970'

Christopher Conner November 02, 2000

I'm sorry but your syntax is completely wrong.

update faq
where created=convert(datetime,convert(char(10),created,101))


You're missing the SET keyword and your column would not have the right results. Say for instance you have a date 1/1/2000 11:00:00 PM. Your code would produce, first of nothing, but if it were fixed it would produce 1/1/2000 1 which would be seen as 1/1/2000 1:00:00 AM.

Daniel Janik March 06, 2001

It should be:
update <table>
set <column>=convert(datetime,convert(char(10),<column>,101))

Björn Österman April 07, 2001

Hi there !
I am using SQL 6.5 , my problem is , I am passing two dates into a stored procedure. My stored prcedure looks like this

CREATE PROCEDURE rep1air_tk(@Debtor_no int,
@Date1 varchar(11),
@Date2 varchar(11))

AS

declare @sql1 varchar(255),
@sql2 varchar(255),
@sql3 varchar(255),
@sql4 varchar(255),
@sql5 varchar(255),
@sql6 varchar(255),
@sql7 varchar(255)





select @sql1 = ' select '
select @sql2 = ' b.collect_dt,b.car_grp,b.Days'
select @sql3 = ' a.inv_crn_no ' + ' from'
select @sql4 = ' rep1debtrans'+ convert(char(6),@Debtor_no)+ ' a ' + ', rep1car'+ convert(char(6), @Debtor_no)+ ' b'
select @sql5 = ' Where a.inv_crn_no = b.inv_crn_no '
select @sql6 = ' and a.collect_dt >= "'+ convert(varchar(11),@Date1) +'"'
select @sql7 = ' and a.collect_dt >= "'+ convert(varchar(11),@Date1) +'"'

exec(@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7)

This work well when I call my stored procedure from isql/w ,
from vb it gives run-time error '-2147217900(80040e14)'
[Microsoft][odbc sql server driver]
[sql sever] invalid column name 'feb 03 2000'

and my sqlString from vb looks like this

sqlString = "rep1air_Tk " & Dbt_no & "," & "'feb 03 2000'" & "," & "'jan 31 2001'"

Set rsTemp = objCon.Execute(sqlString)

Thanking you in advance

Kenneth

Kenneth April 18, 2001

Which is the date format ?

dd/mm/yyyy or mm/dd/yyyy

into the condition ?

Solino Giuseppe June 01, 2001

How i can insert mutlple rows in table like in oracle &colname

ahmed June 29, 2001

How can I convert a date format to mm/yyyy.

And where is there a listing of style to use such as 101.

Charlie Gomez August 29, 2001

I can't get this to work I get an error statement

Jarod October 04, 2001

Where do I input this statement? Do I add this into "Constraint expression"?

Mike November 12, 2001

How can i convert a sql datetime into a date, using MSQUERY, for Excel 97?

Bru CARLONI February 21, 2002