Need help converting date format.

Hi and thanx for reading my post..

I have a reg_date field in my MSSQL DB which is formatted like this :

dd.mm.yy tt:mm:ss (eks. 24.12.03 18:00:03)

What i want to do is get the 8 first chars from this string so i end up with only : 24.12.03

Have tried different variations of : convert(char(8) but not sure how i do this really..

Have already searched the net for a solution but had to post it since i didn't find anything useful..

Hope someone can help me out

Best regards
Mirador-/select ((CONVERT(CHAR(6),getdate(),104)) + RIGHT(CONVERT(CHAR(10),getdate(),104),2))|||select (CONVERT(CHAR(10),getdate(),4))

using 104 adds the century to the year, which then has to be eliminated in the first response. Just using 4 returns only the last two digits of the year. Be carefule though, as this type of date format will be a problem in your code in 96 years ;-)|||Sorry, make that CHAR(8).|||Hi and thanx for the quick reply !

Have tried the code but still got trouble with this errormsg i get :

--> Incorrect syntax near '.03.'

This is the code :

SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) LIKE " & strTheDate.

ps!.. (strTheDate is asp variable..)

I have debugged the strTheDate and i know it's the correct format : 24.12.03 ex.

ps!.. the field is a DateTime format, just to ensure that :)

Best reagards
Mirador|||Can;t say I think this is the best way of doing this, but I haven't had enough coffee to do a re-write (and blindman will undercut me anyway).

Try this (note the single quotes!):

SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) = CONVERT(char(8), CAST('" & strTheDate & "' AS DATETIME),4)

This will convert strTheDate to the same format.

Need function for padding

Hi

I am using Format function in my query when I was using access tables.

Now I have imported my tables in Sql server and linked through odbc. Now in my query it throws message for "Format" function. I tried for Replicate function but still not able to get this problem.

Same case for Date and chr.you need to use convert or cast, not format.|||it sounds like you are doing SQL Passthrough in jet against sql server, and now you need to use SQL Server syntax instead of jet/access sql.'

cheers|||Originally posted by eisoffind
Hi

I am using Format function in my query when I was using access tables.

Now I have imported my tables in Sql server and linked through odbc. Now in my query it throws message for "Format" function. I tried for Replicate function but still not able to get this problem.

Same case for Date and chr.

If i understand your question correctly, you want to be able to pad the left of a number with another value. For example '555' --> '000555'

Attached are two SQL2K UDFs to lpad and rpad.sql

Need an expression format to put decimals .00

I have the following field(Fields!SequenceNO.Value), coming form database.

i would like to have an expression IIF...

if the value coming from database is just 1, then make it 1.00 or

if the value coming from database has a period or point say 1.01 then show as it is.

thank you very much for the help / information....

Hello,

You can do this a couple different ways...

1. Select your textbox that will hold Fields!SequenceNO.Value, then change the Format property to #.00

or

2. Replace Fields!SequenceNO.Value with the expression: =Format(Fields!SequenceNO.Value, "#.00")

Both of these will make all of your numbers in the column have 2 decimals.

Hope this helps.

Jarret

Need a good start on matrix type report, please

I've got the following data I need to report in this format:
Stage P Screen RE-Screen GrdTotal
user1 1 2
3
user2 2 1
6
user3 1 2 1
4
From a sproc the data looks like:
User Type
user1 Stage
user1 RE-Screen
user1 RE-Screen
user2 P-Screen
user2 P-Screen
User2 RE-Screen
I created a report with a subreport for each type count but I haven't been
able to
figure out a way to total up the output from the Sub-Reports. In Crystal I
think I remember passing a gloval value between the report and sub. From
reading on this forum it sounds like there is no way to do this. If you know
how please let me know, then I would have it.
The other ideas is mabe I should have a different SQL and do the
accumulating before I enter the report or I'm gettign ready to write the
report with a grouping on each type. The report has 6 types however.
I haven't worked with Matrix's much and that data regions seems to fit this
need, but...what do you all think?
THANKS!!!
Robert HansenA matrix would exactly do what you want.
The User field would become a matrix row grouping (just drop the field on
row group section of the matrix). The Type field would be a matrix column
grouping with a subtotal (after dropping the field into the column group
section, right click and select "Subtotal"). For the matrix cell you would
use an expression like =Count(Fields!User.Value).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
> I've got the following data I need to report in this format:
> Stage P Screen RE-Screen GrdTotal
> user1 1 2
> 3
> user2 2 1
> 6
> user3 1 2 1
> 4
> From a sproc the data looks like:
> User Type
> user1 Stage
> user1 RE-Screen
> user1 RE-Screen
> user2 P-Screen
> user2 P-Screen
> User2 RE-Screen
> I created a report with a subreport for each type count but I haven't been
> able to
> figure out a way to total up the output from the Sub-Reports. In Crystal
> I
> think I remember passing a gloval value between the report and sub. From
> reading on this forum it sounds like there is no way to do this. If you
> know
> how please let me know, then I would have it.
> The other ideas is mabe I should have a different SQL and do the
> accumulating before I enter the report or I'm gettign ready to write the
> report with a grouping on each type. The report has 6 types however.
> I haven't worked with Matrix's much and that data regions seems to fit
> this
> need, but...what do you all think?
> THANKS!!!
> Robert Hansen|||Thanks!!! Robert
The one thing about a matrix that is throwing me for a loop, is that I need
to display all my Users even if thay have NO activity records (That is
within a data
range) I need their name to show up with blanks counts and 0 GrdTot.
I can do this so nicely with subreports and a real simple table of all users
to drive the report to make all user names show on each row.
I haven't been able to write an SQL to get the data in that form, where I've
got all Users with a row in my dataset. Is there any custom programming
way to get the totals from the subreports? I'm going to try to create a
more complex SQL to get the data I need for thre port so it can include ALL
USERS.
Thanks again for your help!!!
--
Robert Hansen
"Robert Bruckner [MSFT]" wrote:
> A matrix would exactly do what you want.
> The User field would become a matrix row grouping (just drop the field on
> row group section of the matrix). The Type field would be a matrix column
> grouping with a subtotal (after dropping the field into the column group
> section, right click and select "Subtotal"). For the matrix cell you would
> use an expression like =Count(Fields!User.Value).
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
> news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
> > I've got the following data I need to report in this format:
> >
> > Stage P Screen RE-Screen GrdTotal
> > user1 1 2
> > 3
> > user2 2 1
> > 6
> > user3 1 2 1
> > 4
> >
> > From a sproc the data looks like:
> > User Type
> > user1 Stage
> > user1 RE-Screen
> > user1 RE-Screen
> > user2 P-Screen
> > user2 P-Screen
> > User2 RE-Screen
> >
> > I created a report with a subreport for each type count but I haven't been
> > able to
> > figure out a way to total up the output from the Sub-Reports. In Crystal
> > I
> > think I remember passing a gloval value between the report and sub. From
> > reading on this forum it sounds like there is no way to do this. If you
> > know
> > how please let me know, then I would have it.
> >
> > The other ideas is mabe I should have a different SQL and do the
> > accumulating before I enter the report or I'm gettign ready to write the
> > report with a grouping on each type. The report has 6 types however.
> >
> > I haven't worked with Matrix's much and that data regions seems to fit
> > this
> > need, but...what do you all think?
> >
> > THANKS!!!
> > Robert Hansen
>
>|||If you want all users to show up, you need to use an OUTER JOIN in your SQL
query to get them into the resulting data set. Depending on the data source,
the actual SQL syntax for outer joins will vary.
You will also need to modify the expression in the matrix cell to take into
account those users without activity, but showing up in the data set.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
news:7735073D-1D95-4D72-B66A-AE56F696E2EE@.microsoft.com...
> Thanks!!! Robert
> The one thing about a matrix that is throwing me for a loop, is that I
> need
> to display all my Users even if thay have NO activity records (That is
> within a data
> range) I need their name to show up with blanks counts and 0 GrdTot.
> I can do this so nicely with subreports and a real simple table of all
> users
> to drive the report to make all user names show on each row.
> I haven't been able to write an SQL to get the data in that form, where
> I've
> got all Users with a row in my dataset. Is there any custom programming
> way to get the totals from the subreports? I'm going to try to create a
> more complex SQL to get the data I need for thre port so it can include
> ALL
> USERS.
> Thanks again for your help!!!
> --
> Robert Hansen
>
> "Robert Bruckner [MSFT]" wrote:
>> A matrix would exactly do what you want.
>> The User field would become a matrix row grouping (just drop the field on
>> row group section of the matrix). The Type field would be a matrix column
>> grouping with a subtotal (after dropping the field into the column group
>> section, right click and select "Subtotal"). For the matrix cell you
>> would
>> use an expression like =Count(Fields!User.Value).
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
>> news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
>> > I've got the following data I need to report in this format:
>> >
>> > Stage P Screen RE-Screen GrdTotal
>> > user1 1 2
>> > 3
>> > user2 2 1
>> > 6
>> > user3 1 2 1
>> > 4
>> >
>> > From a sproc the data looks like:
>> > User Type
>> > user1 Stage
>> > user1 RE-Screen
>> > user1 RE-Screen
>> > user2 P-Screen
>> > user2 P-Screen
>> > User2 RE-Screen
>> >
>> > I created a report with a subreport for each type count but I haven't
>> > been
>> > able to
>> > figure out a way to total up the output from the Sub-Reports. In
>> > Crystal
>> > I
>> > think I remember passing a gloval value between the report and sub.
>> > From
>> > reading on this forum it sounds like there is no way to do this. If
>> > you
>> > know
>> > how please let me know, then I would have it.
>> >
>> > The other ideas is mabe I should have a different SQL and do the
>> > accumulating before I enter the report or I'm gettign ready to write
>> > the
>> > report with a grouping on each type. The report has 6 types however.
>> >
>> > I haven't worked with Matrix's much and that data regions seems to fit
>> > this
>> > need, but...what do you all think?
>> >
>> > THANKS!!!
>> > Robert Hansen
>>

Nb Months between 2 dates (one in DB2 format)

How can I get the number of months (or years or days...)
between the current_timestamp
an a date in the DB2 format ?
DateDB2 Char(26) = 2000-12-30-12.55.30.123456
DateSQL = GetDate() or Current_Timestampdatediff (mm,convert (datetime , substring(datedb2,1,10)),DateSQL )|||nope,

I've tried that sort of conversion but it seems
that the CONVERT(DateTime,...) function inverts
the month and the day

Convert(DateTime,'2000-12-30') >>> doesn't work !!!
Convert(DateTime,'2000-30-12') >>> works !!!

Is '2000-30-12' a standard English-Format-Date ?|||Convert(DateTime,'2000-12-30') >>> doesn't work !!!
Then try this >>> select Convert(DateTime,'2000-12-30',120)|||Thank you very much Enigma !