converting datetime for comparison

I am passing a datetime to a stored proc and I need to convert this
datetime to the format dd/mm/yyyy and then make a comparison between it
and a field in my select statement. Can somebody tell me what I am
doing wrong in my code?
declare @.Company varchar(50)
declare @.SerialNumber varchar(50)
declare @.ProductGroup varchar(10)
declare @.InstallationDate datetime
set @.Company = ''
set @.SerialNumber = ''
set @.ProductGroup = '9'
set @.InstallationDate = '21/03/2003 00:00:00'
select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
p.InstallationDate,
t.ProductTypeID, t.[Description], convert(datetime,
left(p.InstallationDate, 11), 103),
convert(datetime, left(@.InstallationDate, 11), 103)
from Products p
inner join companysite s
on p.OwnedByCompanyID = s.CompanySiteKey
inner join Competitors c
on c.CompetitorID = p.ProducedByID
inner join CompetitorMachines m
on p.MachineID = m.CompetitorMachineID
inner join ProductTypes t
on t.ProductTypeID = m.ProductTypeID
where s.[name] like '%' + @.Company + '%'
and p.SerialNumber like '%' + @.SerialNumber + '%'
and t.ProductTypeID like '%' + @.ProductGroup + '%'
and convert(char(11), left(p.InstallationDate, 11), 103) =
convert(char(11), left(@.InstallationDate, 11), 103)
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" wrote ...

> Can somebody tell me what I am doing wrong in my code?
A guess only...but..

> declare @.InstallationDate datetime
^^ a datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'

> convert(char(11), left(@.InstallationDate, 11), 103)
char(11)
Perhaps the different data types is the problem?
Something else you could try which might help you achieve the same result
would be using the DATEDIFF function, if you are trying to find matches for
the same day, just use DATEDIFF, specify days, and look for where it equals
0 (ie, no days difference)..
Hope this helps..
Rob|||I have some best practices for datetime here: http://www.karaszi.com/SQLServer/in...
o_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike P" <mike.parr@.gmail.com> wrote in message news:e3zsKC2nGHA.3348@.TK2MSFTNGP03.phx.gbl.
.
>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
> declare @.Company varchar(50)
> declare @.SerialNumber varchar(50)
> declare @.ProductGroup varchar(10)
> declare @.InstallationDate datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'
> select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
> p.InstallationDate,
> t.ProductTypeID, t.[Description], convert(datetime,
> left(p.InstallationDate, 11), 103),
> convert(datetime, left(@.InstallationDate, 11), 103)
> from Products p
> inner join companysite s
> on p.OwnedByCompanyID = s.CompanySiteKey
> inner join Competitors c
> on c.CompetitorID = p.ProducedByID
> inner join CompetitorMachines m
> on p.MachineID = m.CompetitorMachineID
> inner join ProductTypes t
> on t.ProductTypeID = m.ProductTypeID
> where s.[name] like '%' + @.Company + '%'
> and p.SerialNumber like '%' + @.SerialNumber + '%'
> and t.ProductTypeID like '%' + @.ProductGroup + '%'
> and convert(char(11), left(p.InstallationDate, 11), 103) =
> convert(char(11), left(@.InstallationDate, 11), 103)
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
Well, what is the problem? Do you get an error message, or is it not
returning any rows? Could you give us DDL, sample data, and desired results
as per http://www.aspfaq.com/5006 ?

CONVERT() FUNCTION

i want a datetime format as mm/dd/yyyy in dataset i am getting
AcquisitionDate="2005-05-05T00:00:00+05:30"

if i used convert(datetime,AcquisitionDate) function ,the sql is throwing error convertion failed to datetime as character string

any other way to convert this

guide me please

SQL Server's datetime does not store timezone as part of the datetime format.

To store this data, you can either strip off the time zone portion (using SUBSTRING in sql or Substring in .Net) and store as is. If the timezone is important to you, you can normalize the time with respect to a standard time zone and then strip the timezone off.

-Todd

convert year from mm/dd/yyyy to mm/dd/yy

I am working with a chart. I want to display the year on the X axis as mm/dd/yy. I have tried entering yy, etc into the format code field and have had no luck.

Any comments are appreciated.

Reporting Services uses similar formatting commands to Excel. You should be able to use MM/dd/yy.

If that doesn't work, another way would be to create a function that would return the values in a string format and parse the string. Something like:

public function getDate(dt as string) as string

string dt = left(dt,6) & right(dt,2)

return dt

end function

cheers,

Andrew

|||

Thanks! I was able to get this to work

CONVERT(varchar(12), field_name, 10) AS Date

Convert varchar to datetime!

Hi:

I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime.

I use Query like:

SELECT

CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +
CAST(SUBSTRING(date_sent,4,2) as int) + '/'+CAST(SUBSTRING(DATE_SENT,7,4) as int)

From MyTable

It is not working, anybody can give me some advise!

thanks!

DECLARE @.Date_Sent CHAR(10)
SET @.Date_Sent = '28/02/2004'
SELECT CONVERT(DATETIME, @.Date_Sent, 103)|||

Try:

SELECT

Cast(SUBSTRING(Date_Sent,4,2) + '/' +
SUBSTRING(date_sent,1,2) + '/'+SUBSTRING(DATE_SENT,7,4) as datetime)

|||

The solution fromPDraigh worked for me .

Thanks

really should have designed database to use datetime in first place , but this is a nice work around

|||

Strange !! it gave me an error in the SQL statement !!

my field is a varchar

convert to T-SQL

Can anybody convert to T-SQL;
select distinct to_char(sysdate,'FMDAY, MONTH DD, YYYY HH:FMMIAM') TODAY
, d_aaa_insthdg CNTRHD
, decode(trunc((d_vst_btcdte - nvl(n_per_birth,d_vst_btcdte))/10000,0),0,'no DOB',
14,'14-16',15,'14-16',16,'14-16',
17,'17-20',18,'17-20',19,'17-20',20,'17-20',
21,'21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25',
26,'26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30',
31,'31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35',
36,'36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40',
41,'41-45',42,'41-45',43,'41-45',44,'41-45',45,'41-45',
46,'46-50',47,'46-50',48,'46-50',49,'46-50',50,'46-50',
51,'51-55',52,'51-55',53,'51-55',54,'51-55',55,'51-55',
56,'56-60',57,'56-60',58,'56-60',59,'56-60',60,'56-60',
61,'61-65',62,'61-65',63,'61-65',64,'61-65',65,'61-65',
66,'66-70',67,'66-70',68,'66-70',69,'66-70',70,'66-70',
'71+') AGE
, sum(decode(d_vst_dontyp,'WB',1,0)) DRAW1
, sum(decode(d_vst_dontyp,'xx',1,1)) TOTAL0Didn't I already do this for you?

Convert Error

I am trying to convert a varchar(10) into a smalldatetime field in a view.

The table holds the date information in the format dd/mm/yyyy in a
varchar(10).

I can convert most fields in this format in my view using;

CONVERT (smalldatetime, ActualInDate, 103)

However one field fails, I have some dates as 02/01/0000, this is because it is a blank system date which is output from my mainframe job to extract data from another server.

Can I convert these blank dates to sql blank dates in my convert statement, ie replace these dates from 02/01/0000 to 01/01/1900.

The error I get is the error below;

Database Server: Microsoft SQL Server
Version 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime datatype resulted in an out-of-range small datetime value.

Cheers,

JonMy guess would be that you are having problems with different data rows than you think are causing the problems. Use the IsDate() function to show you the "problem children" in your data. I suspect that your server is interpreting the dates as MM/DD/YYYY instead of dd/mm/yyyy as you expect. If that is the case, I'd convert the whole bunch to ISO standard format of YYYY-MM-DD (like 1900-01-01), which is a lot harder to misinterpret!

-PatP|||This appears to work:

CREATE TABLE MyTable
(
ActualInDate varchar(20)
)

INSERT INTO MyTable(ActualInDate)
VALUES ('02/01/0000')
INSERT INTO MyTable(ActualInDate)
VALUES ('13/02/1975')

SELECT CONVERT (smalldatetime, (CASE WHEN ActualInDate = '02/01/0000' THEN '01/01/1900' ELSE ActualInDate END), 103)
FROM MyTable|||Yup, it appears, but appearances are misleading...

SELECT CONVERT (smalldatetime, (
case
when charindex('0000', ActualInDate) > 0 then '01/01/1900'
else case
when isdate(ActualInDate) = 0 then '01/01/1900'
else ActualInDate end
end), 103)
FROM MyTable|||Thanks for the ideas but I just used this to do it and it seems fine:

CONVERT (smalldatetime, REPLACE(ActualIn, '02/01/0000', '01/01/1900'), 103)

Cheers|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.

He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.

He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.I don't think that's his sole purpose in life, but rdjabarov often comes across that way. I've just had to develop thicker skin where his posts are concerned.

He really does know what he's doing (at least as often as most of us do anyway :)), but he comes across pretty abrasively at time.

-PatP|||Well there's the other side of the coin as well...you have to take everything with a grain of salt...because

IT JUST DOESN'T MATTER

Speaking of which...where's Rudy?|||I did not say he did'nt know what he was talking about. I was objecting to the snide remarks

I am sorry.
I am cranky ass.
Quitting smoking.
Unrealistic deadline.
Little sleep.
Stressed from a couple job interviews.
Too easily offended from my hot southern celtic blood.
I apologize.|||Speaking of which...where's Rudy?speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood

;)|||i was actually enjoying the intellectual dialogue in this thread

I'm still looking...|||speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood

;)Yeah, but it does help to put things into context!

-PatP|||Pat does contexts ;)

Convert datetime to char/nvarchar or the opposite (in specific for

I have a table with a column name Tdate, data type datetime, and length 8.
The look of the existing data in this column is from this format
DD/MM/YYYY HH: MI: SS.
I want to pass to a stored procedure string and another data that will
update a specific row, where the string I pass is equal to the value in the
column.
That’s why I want to convert the value of the column from datetime to
nvarchar.
My problem is I do not find the date format I wrote above – I searched in
sql books online and over the Internet.
I tried the following code (because it looks the most similar to the format)
and more others.
CONVERT(CHAR(30), Tdae, 131)='11/05/2006 12:20:35'
Can you please tell me how can I convert the datetime to nvarchar that will
look like that format?
Or – convert the string that looks like the format above to datetime from
that format.
Thanks in advance!Hi,
Converting datetime to varchar for comparison is not a good option.
But you can convert varchar to datetime.
Can't you use somthing like this for comparison?
Tdae = cast('11/05/2006 12: 20: 35' as datetime)
Or have I understood the question wrong?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Convert it the other way around instead. See http://www.karaszi.com/SQLServer/in...
_datetime.asp for
elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niron kag" <Nironkag@.discussions.microsoft.com> wrote in message
news:A082F020-4217-4571-9E52-C6A8F91E65F8@.microsoft.com...
>I have a table with a column name Tdate, data type datetime, and length 8.
> The look of the existing data in this column is from this format
> DD/MM/YYYY HH: MI: SS.
> I want to pass to a stored procedure string and another data that will
> update a specific row, where the string I pass is equal to the value in th
e
> column.
> That’s why I want to convert the value of the column from datetime to
> nvarchar.
> My problem is I do not find the date format I wrote above – I searched i
n
> sql books online and over the Internet.
> I tried the following code (because it looks the most similar to the forma
t)
> and more others.
> CONVERT(CHAR(30), Tdae, 131)='11/05/2006 12:20:35'
> Can you please tell me how can I convert the datetime to nvarchar that wil
l
> look like that format?
> Or – convert the string that looks like the format above to datetime fro
m
> that format.
> Thanks in advance!
>