Showing posts with label expression. Show all posts
Showing posts with label expression. Show all posts

Thursday, March 22, 2012

converting a number into a date type using an expression

I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!

Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.

Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.

I'm just getting started with SQL 2005 so I don't know how to do this using an expression.

Any help would be greatly appreciated.

Thanks,

Gray

I think your approach sounds valid. You're going to have to parse it with substring and build it into a date format. Then you can cast the string to a date/time field.

(DT_DBTIMESTAMP)(substring([YourColumn + 19000000],x,y) + "/" + .......)

|||

Hi Phil,

Thanks for the info ... it really helped ... here's my final expression ...

(DT_DBTIMESTAMP)(SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),5,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),7,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),1,4))

Thanks again,

Gray

Converting a date in the form of a string to a datetime format

Hi,

I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:

'01/'&Month(OrderDate)&'/'&Year(OrderDate)

Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.

There is a CONVERT function, but I'm new to all this and can't seem to get it to work.

Any help would be gratefully received!

Cheers!

Keith

you can group by without having to transform the date and that is

done like the example below

use northwind
select year(orderdate) as year, month(orderdate) as month , count(*)as ordercount from orders
group by year(orderdate),month(orderdate)
order by 1,2

|||

Try,

select '01/'+convert(varchar(2), month(OrderDate))+'/06' from Table

|||the year shouldn't be static... just add convert(varchar(2), year(OrderDate)) in place of 06|||

Cheers!

I'll give these a try.

Is the plus symbol '+' used in place of '&' to concatenate strings together in transact SQL then?

Also, I looked up varchar(2) in my ASCII list and it denotes 'start of text'. Why is this necessary out of interest?

Many thanks indeed!

Keith

|||

Ok, I've given the following a go:

SELECT '01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate)) AS 'OrderMonth'

It runs, but this is the result:

01/12/*

For some reason, the year is represented by a *. Any ideas why this may be?

Also, the expression as it is above still brings back a date (albeit with a * for the year) in a string format. Ideally, I want to convert all of that to a date format so it behaves like a date. To that end, I then took the above a bit further and attempted this:

SELECT CONVERT(Datetime,'01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate))) AS 'OrderMonth'

This basically encapsulates the exact expression above within a 'top level' CONVERT statement to convert it to datetime. I get the following run time error message though:

'Syntax error converting datetime from character string'. It's probably due to the *. To someone on here, it's probably obvious what I'm doing wrong.

Any ideas?

Many thanks!!!

|||

What about this:

SELECT DATEADD(mm, DATEDIFF(mm,0,orderdate), 0) as firstdayofOrdersDates , count(*)as ordercount from orders

group by DATEADD(mm, DATEDIFF(mm,0,orderdate), 0)

|||

Hi limno,

Whilst your illustration doesn't quite bring back what I need, it has pointed the way to exactly what I need! See below what I have used:

SELECT DateAdd(Day,-Day(OrderDate)+1,OrderDate) AS 'OrderMonth'

Essentially what this is doing is dedecting the number of days into the month for a particular order date to get back to the last day of the previous month. I then add on 1 to give me the first day of the current month. Using this in the GROUP BY statement then nicely groups all my orders by month!

So really, I didn't need to do any converting of string expressions after all. I can't believe I didn't think of this before. It must have been staring me in the face!

Cheers for your help!

Converting a date in the form of a string to a datetime format

Hi,

I'm writing some SQL and want to convert the following expression into a date format in the SELECT list:

'01/'&Month(OrderDate)&'/'&Year(OrderDate)

Basically, I want to look at each order date, convert that date to the first of that month and GROUP BY this expression.

There is a CONVERT function, but I'm new to all this and can't seem to get it to work.

Any help would be gratefully received!

Cheers!

Keith

you can group by without having to transform the date and that is

done like the example below

use northwind
select year(orderdate) as year, month(orderdate) as month , count(*)as ordercount from orders
group by year(orderdate),month(orderdate)
order by 1,2

|||

Try,

select'01/'+convert(varchar(2),month(OrderDate))+'/06'from Table

|||the year shouldn't be static... just add convert(varchar(2), year(OrderDate)) in place of 06|||

Cheers!

I'll give these a try.

Is the plus symbol '+' used in place of '&' to concatenate strings together in transact SQL then?

Also, I looked up varchar(2) in my ASCII list and it denotes 'start of text'. Why is this necessary out of interest?

Many thanks indeed!

Keith

|||

Ok, I've given the following a go:

SELECT '01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate)) AS 'OrderMonth'

It runs, but this is the result:

01/12/*

For some reason, the year is represented by a *. Any ideas why this may be?

Also, the expression as it is above still brings back a date (albeit with a * for the year) in a string format. Ideally, I want to convert all of that to a date format so it behaves like a date. To that end, I then took the above a bit further and attempted this:

SELECT CONVERT(Datetime,'01/'+CONVERT(varchar(2),Month(OrderDate))+'/'+CONVERT(varchar(2),Year(OrderDate))) AS 'OrderMonth'

This basically encapsulates the exact expression above within a 'top level' CONVERT statement to convert it to datetime. I get the following run time error message though:

'Syntax error converting datetime from character string'. It's probably due to the *. To someone on here, it's probably obvious what I'm doing wrong.

Any ideas?

Many thanks!!!

|||

What about this:

SELECTDATEADD(mm,DATEDIFF(mm,0,orderdate), 0)as firstdayofOrdersDates ,count(*)as ordercount from orders

groupbyDATEADD(mm,DATEDIFF(mm,0,orderdate), 0)

|||

Hi limno,

Whilst your illustration doesn't quite bring back what I need, it has pointed the way to exactly what I need! See below what I have used:

SELECT DateAdd(Day,-Day(OrderDate)+1,OrderDate) AS 'OrderMonth'

Essentially what this is doing is dedecting the number of days into the month for a particular order date to get back to the last day of the previous month. I then add on 1 to give me the first day of the current month. Using this in the GROUP BY statement then nicely groups all my orders by month!

So really, I didn't need to do any converting of string expressions after all. I can't believe I didn't think of this before. It must have been staring me in the face!

Cheers for your help!

sqlsql

Converting @[System::StartTime] to String

I am trying to get the System::StartTime into a table, but I get this error in my expression builder saying "Cannot convert 'System.DateTime' to 'System.String'

I have tried (DT_TEXT, 1252) @.[System::StartTime] but I get the following

Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft.DataTransformationServices.Controls)

I also tried (DT_STR, 23, 1252) @.[System::StartTime] and I get the following error

The expression " (DT_STR, 23, 1252) @.[System::StartTime]" has a result type of "DT_STR", which cannot be converted to a supported type.

Any help?In which task are you trying to do this?
Because when I have this in my Execute SQL task:
"INSERT INTO temp (tempText) VALUES ('" + (DT_STR, 23, 1252) @.[System::StartTime] + "')"
As expression for sql statement it processes fine|||I am doing it is a Execute SQL task.

Can you tell me did you put the in the expression or is that directly in the SQL statement?|||

TheViewMaster wrote:

In which task are you trying to do this?
Because when I have this in my Execute SQL task:
"INSERT INTO temp (tempText) VALUES ('" + (DT_STR, 23, 1252) @.[System::StartTime] + "')"
As expression for sql statement it processes fine

Sorry I just saw this

"As expression for sql statement it processes fine"

Sunday, March 11, 2012

Convert String to Numeric

Hi all,

I defined an user string type varible in the package as AccountLen. I am trying to use this varible in the Expression of Derived Column transformation.

I want to retrieve a part of column, i.e: Right(Column1, @.AccountLen), this is always wrong because the AccountLen is string type. How I can convert it to the numeric so that can be used in the RIGHT function?

Thanks

To convert a string to numeric you CAST the field.

(DT_I4)@.[User::AccountLen]

However, I don't believe that will work as I don't believe most functions accept variables as input parameters.

|||

Thank you very much. Your solution is working fine.

Sunday, February 19, 2012

CONVERT IN SELECT

Is it possible to covert a smalldatetime in a select statement using a
regular expression of some kind?
My dates are stored as:
7/30/2005 6:58:00 PM
I need to sort and group the dates by hour but I need to continue to store
the minute. Can I envoke the use of a regular expression in a convert
function? If that's possible, could someone post an example? I don't know
much about regular expressions in SQL.
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
See http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>
|||Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field>)
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Ian O'Betz" wrote:

> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>
|||I forgot to mention this in my first posting, but I need the date as:
7/30/2005 6:00 PM if the actual date is 7/30/2005 6:58 PM
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:6E3E1C6E-06F1-4D5E-9EFB-2D926D0CB459@.microsoft.com...
Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field>)
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Ian O'Betz" wrote:

> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>
|||Perfect, using DateAdd() was my solution. thanks for your help.
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxxjwr$mFHA.3380@.TK2MSFTNGP12.phx.gbl...
See http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>

CONVERT IN SELECT

Is it possible to covert a smalldatetime in a select statement using a
regular expression of some kind?
My dates are stored as:
7/30/2005 6:58:00 PM
I need to sort and group the dates by hour but I need to continue to store
the minute. Can I envoke the use of a regular expression in a convert
function? If that's possible, could someone post an example? I don't know
much about regular expressions in SQL.
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.netSee http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field> )
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ian O'Betz" wrote:

> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||I forgot to mention this in my first posting, but I need the date as:
7/30/2005 6:00 PM if the actual date is 7/30/2005 6:58 PM
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:6E3E1C6E-06F1-4D5E-9EFB-2D926D0CB459@.microsoft.com...
Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field> )
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ian O'Betz" wrote:

> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||Perfect, using DateAdd() was my solution. thanks for your help.
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxxjwr$mFHA.3380@.TK2MSFTNGP12.phx.gbl...
See http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>

CONVERT IN SELECT

Is it possible to covert a smalldatetime in a select statement using a
regular expression of some kind?
My dates are stored as:
7/30/2005 6:58:00 PM
I need to sort and group the dates by hour but I need to continue to store
the minute. Can I envoke the use of a regular expression in a convert
function? If that's possible, could someone post an example? I don't know
much about regular expressions in SQL.
--
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.netSee http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field>)
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ian O'Betz" wrote:
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||I forgot to mention this in my first posting, but I need the date as:
7/30/2005 6:00 PM if the actual date is 7/30/2005 6:58 PM
--
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:6E3E1C6E-06F1-4D5E-9EFB-2D926D0CB459@.microsoft.com...
Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field>)
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ian O'Betz" wrote:
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||Perfect, using DateAdd() was my solution. thanks for your help.
--
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxxjwr$mFHA.3380@.TK2MSFTNGP12.phx.gbl...
See http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>

Sunday, February 12, 2012

convert expression in SQL RS?

Hello:
How to convert this Crystal report formula to an
expression in SQL RS ?
if {@.Date} <> minimum({@.Date}) then
(({ByMonth.CustomerCount} - previous({ByMonth.CustomerCount})) /
previous({ByMonth.CustomerCount})) * 100
Thank you.On Aug 12, 1:40 pm, GGill <GG...@.discussions.microsoft.com> wrote:
> Hello:
> How to convert this Crystal report formula to an
> expression in SQL RS ?
> if {@.Date} <> minimum({@.Date}) then
> (({ByMonth.CustomerCount} - previous({ByMonth.CustomerCount})) /
> previous({ByMonth.CustomerCount})) * 100
> Thank you.
Assuming that @.Date is a separate dataset (sourced via query/stored
procedure) used as a hidden parameter, etc.
Something like this should work.
=iif(Parameters!Date.Value <> Min(Parameters!Date.Value), ((Fields!
CustomerCount.Value - Previous(Fields!CustomerCount.Value))/
Previous(Fields!CustomerCount.Value)) * 100, Nothing)
If this does not fit your needs, you may want to set a flag field of
some sort (in the stored procedure/query that is sourcing the report)
to check against instead of a hidden parameter, etc.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant