date - SQL Server Forums

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)

date

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 09:59:59

Hello,
There is a field called ProcessDate in tblMain
This field is of type smalldatetime

The values in there are like:
2010-03-02 00:00:00
2010-01-28 00:00:00
2010-02-02 00:00:00
2009-11-02 00:00:00

if I pass a date as a string to my query i.e. '03/02/2010' then how do I convert this varchar(20) parameter to look at the correct date in the tblMain?
Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-29 : 10:04:07

You dont have to, SQL server will store it in the yyyy-mm-dd hh:mm:ss format if you pass the date in a mm/dd/yyyy format.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 10:05:16

it seems I have to do this:
select convert(smalldatetime, @Date, 101)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-29 : 10:05:55

You should always pass the date in unambiguios format YYYYMMDD HH:MM:SS

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-29 : 10:20:29

quote:
Originally posted by arkiboys

it seems I have to do this:
select convert(smalldatetime, @TradeDate, 101)

You don't have to..Run this and check

declare @t table(ProcessDate smalldatetime)
insert @t
select '03/02/2010'
union all select '2010-03-02'
union all select '20100302'

select * from @t

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 10:34:14

I am get this date from UI "07/02/2010"
And I am passing it to the stored procedure so that in the table it gets stored as 2010-02-05 which represents 5th of Feb 2010
How should the sql be to format this date and place it into the table?
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 10:35:20

quote:
Originally posted by vijayisonly

quote:
Originally posted by arkiboys

it seems I have to do this:
select convert(smalldatetime, @TradeDate, 101)

You don't have to..Run this and check
declare @t table(ProcessDate smalldatetime)
insert @t
select '03/02/2010'
union all select '2010-03-02'
union all select '20100302'

select * from @t

it returns:
2010-03-02 00:00:00
2010-03-02 00:00:00
2010-03-02 00:00:00

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 10:37:35

The last sql should return 02 march 2010 but it does not
declare @t table(ProcessDate smalldatetime)
insert @t
select '03/02/2010'
union all select '2010-03-02'
union all select '20100302'

select * from @t
select * from @t where ProcessDate = '02/03/2010'

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-29 : 10:44:03

Arkiboys,

When you are using an ambiguous date formatted '##/##/####' it is assumed to be MM/DD/YYYY

So your select is asking for Feb, 3 2010 NOT Mar 2 2010.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 10:58:21

Ok, I think, I know what you are saying...
Imagin this is the date that is coming from the UI:
'2010-02-14T00:00:00+00:00'

This has to be stored into ProcessDate field which is of type smalldatetime. So when you select ProcessDate from tblMain it shows:
2010-02-14
Question:
What should the sql be to do this please?
thanks

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-29 : 11:01:37

if Process date is smalldatetime you should be able to return it with 'Where Processdate = '20100214''

Or...

= '2010-02-14'
= '2/14/2010' <- most people caution against using these formats

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 11:19:38

ok, this is what I am trying to retrieve from xml but it gives the conversion error. Do you see why? It is to do with the TradeDate field. Thanks

exec uspAdd XML='
<Date>2010-02-14T00:00:00+00:00</Trade'

set dateformat dmy

declare @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @XML

select
convert(smalldatetime, Date
FROM OPENXML (@idoc, '/dates/date',2)
WITH
( TradeDate varchar(20))

return

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-29 : 11:24:11

First off the length of <TradeDate>2010-02-14T00:00:00+00:00</TradeDate> is longer than 20 characters, 2nd I don't think it knows how to convert the 'T' and '+'. Try and convert(smalldatetime,Left(10, TradeDate)) since it does not look like you care about time.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 11:29:40

I have made changes as you suggested. See below. still the same error:
select
convert(smalldatetime,Left(10, TradeDate)),
FROM OPENXML (@idoc, '/dates/date',2)
WITH
(
TradeDate varchar(20))

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 11:40:36

It is definitely to do with the TradeDate field. Not sure what it is though.
Any thoughts please?
Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-29 : 11:57:09

The left syntax is incorrect..should be Left(TradeDate,10)
Change that and try..it shud work

select 
TradeCode, 
Source, 
convert(smalldatetime,Left(TradeDate,10)), 
TradeType,
Branch,
Org,
AmountUSD,
AccountClassification,
Currency,
[Level]
FROM OPENXML (@idoc, '/Trades/Trade',2)
WITH 
(
TradeCode varchar(20),
Source varchar(20),
TradeDate varchar(20),
TradeType varchar(10),
Branch bigint,
Org int,
AmountUSD decimal(15, 2),
AccountClassification varchar(20),
Currency varchar(5),
[Level] tinyint
)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 12:00:05

It still fails.
It only works if this is being passed in
<TradeDate>14-02-2010</TradeDate>

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-29 : 12:03:05

Again you have to change your length in the with section

WITH
(
TradeCode varchar(20),
Source varchar(20),
TradeDate varchar(20),
TradeType varchar(10),
Branch bigint,
Org int,
AmountUSD decimal(15, 2),
AccountClassification varchar(20),
Currency varchar(5),
[Level] tinyint
)

The entry you are trying to pass is greater than 20 characters, try bumping it to 50 or something. The left will take care of things from there.

2010-02-14T00:00:00+00:00 is 25 Chars long

And thanks vijay for pointing out my function issue.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 12:29:20

This is the error message:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-29 : 12:39:55

Declare @string as Varchar(25)

Set @string = '2010-02-14T00:00:00+00:00'

Select Convert(smalldatetime, left(@string,10))

Works ok, you are going to have to check your values in your table. if you try something like '2010-02-30' you will get an error because it does not exist as a real date. Would your data have any bad elements in it?

OR!

Does your data have a bad date format like '2010-2-14' or '2010-02-4'
where it is not in the format YYYY-MM-DD

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-29 : 12:41:13

All ok now, thank you
I was using setdateformat dmy in my stored procedure and I have taken it out.
Thank you all.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources