Date/Time Stamp: February 2012

Saturday, February 25, 2012

date format

How do I format a date to display as 'dd month yyyy'? ex. '31 October 2004'
ThanksAll formatting should be done client side.
I have a functions in VBscript to format dates, numbers and currency.
Since my pages support French and English,
of course the functions return values depending on user language.
Then I just: response.write formatDateLong(date(), french)
"Arul" <Arul@.discussions.microsoft.com> wrote in message
news:BC460844-A8DC-4502-A2D5-39C3448A6F56@.microsoft.com...
> How do I format a date to display as 'dd month yyyy'? ex. '31 October
> 2004'
> Thanks|||as Raymond mentioned, formating should be done in client app but if you
really wanted to do this in the DB then it would be something like this...
declare @.inDate datetime
set @.indate = '04/30/2004'
select convert(varchar,datepart(dd, @.indate)) + ' '+
datename(mm, @.indate) + ' ' +
convert(varchar,datename(yyyy, @.indate) )
Message posted via http://www.webservertalk.com

Date Format

Hi!!!
My procedure get a date using the function getdate() and insert in the datetime field.
I.e. 04/04/03 09:55:18
I would like format my data without time.
Excuse me my simple english.
Thank you.Hello,

you use Oracle I guess ??

For Oracle use TRUNC(datefield) to TRUNCate the time value.

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Date format

I am trying to convert a number string into a date. I am using:
to_date(settdate,'yyyy/mm/dd') "New date"
in my select statement. I get the error "input value not long enough for date format"
Why do I get the error?
Thanks.You are talking about "number string". Is your variable "settdate" a Julian date ? Can you give us an example value ?

This works for me :

declare
a varchar2(100);
b date;
begin
a := '2450450';
b := to_date(a,'J');
dbms_output.put_line(to_char(b,'DD(MM/YYYY'));
end;
/

SQL>
01/01/1997

SQL>PL/SQL procedure successfully completed.

date format

How can I strip the time from datetime field without converting to Char or
Vchar?You can't. DATETIME and SMALLDATETIME always store both date and time.
What you can do is set the time to midnight. For example:
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
--
David Portas
SQL Server MVP
--|||Date already exists so I am not sure how to utlize
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
to the existing date field.
"David Portas" wrote:
> You can't. DATETIME and SMALLDATETIME always store both date and time.
> What you can do is set the time to midnight. For example:
> SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMESTAMP),0)
> --
> David Portas
> SQL Server MVP
> --
>
>

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
GunSee my reply in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>|||Hi
Read more on convert/cast functions in sql bol
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gunawan" <jgun98.milis@.gmail.com> wrote in message
news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date format

I have a database that is used priamrily by our UK division. The server
default setting is US time date format. Is there a way to set the UK database
to be their date format when ours is USHow about UK division using GETUTCDATE() function?
This will convert the US time to UTC time.
--
Thanks
GYK|||This would be fine. However, we are running everyhting through or ERP system
"GYK" wrote:
> How about UK division using GETUTCDATE() function?
> This will convert the US time to UTC time.
> --
> Thanks
> GYK|||Input or output? If you are talking about format of the date, I suggest you read
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:43049B5F-3F12-43E4-9FA3-BBD860912772@.microsoft.com...
>I have a database that is used priamrily by our UK division. The server
> default setting is US time date format. Is there a way to set the UK database
> to be their date format when ours is US|||I actually wnat it to be stored in the UK Date Format. Can I do this at a
database level or server level
"Tibor Karaszi" wrote:
> Input or output? If you are talking about format of the date, I suggest you read
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:43049B5F-3F12-43E4-9FA3-BBD860912772@.microsoft.com...
> >I have a database that is used priamrily by our UK division. The server
> > default setting is US time date format. Is there a way to set the UK database
> > to be their date format when ours is US
>
>|||As per the article Tibor referenced:
Date and time formats
A common misconception is that SQL Server stores these datatypes in some
particular readable format. That is not the case. SQL Server stores these
values in an internal format (two integers). However, when you use T-SQL to
specify a value (in an INSERT statement, for instance) you express it as a
string. And there are rules for how different datetime string formats are
interpreted by SQL Server. But note that SQL Server does not remember the
format in any way.
You cannot store your date in any format. The stored value is always
location independent and totally unambiguous. Format is only for display and
is determined when you retrieve a datetime value from a table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:00E8FB51-CE79-4BDC-BADA-2B42C4A79EF4@.microsoft.com...
>I actually wnat it to be stored in the UK Date Format. Can I do this at a
> database level or server level
> "Tibor Karaszi" wrote:
>> Input or output? If you are talking about format of the date, I suggest
>> you read
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "DBA" <DBA@.discussions.microsoft.com> wrote in message
>> news:43049B5F-3F12-43E4-9FA3-BBD860912772@.microsoft.com...
>> >I have a database that is used priamrily by our UK division. The server
>> > default setting is US time date format. Is there a way to set the UK
>> > database
>> > to be their date format when ours is US
>>|||So if I want the date format from any app pulling the date from the database.
If we set the OS itself to be a certain date format, then would it always
display in that format
"Kalen Delaney" wrote:
> As per the article Tibor referenced:
> Date and time formats
> A common misconception is that SQL Server stores these datatypes in some
> particular readable format. That is not the case. SQL Server stores these
> values in an internal format (two integers). However, when you use T-SQL to
> specify a value (in an INSERT statement, for instance) you express it as a
> string. And there are rules for how different datetime string formats are
> interpreted by SQL Server. But note that SQL Server does not remember the
> format in any way.
> You cannot store your date in any format. The stored value is always
> location independent and totally unambiguous. Format is only for display and
> is determined when you retrieve a datetime value from a table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:00E8FB51-CE79-4BDC-BADA-2B42C4A79EF4@.microsoft.com...
> >I actually wnat it to be stored in the UK Date Format. Can I do this at a
> > database level or server level
> >
> > "Tibor Karaszi" wrote:
> >
> >> Input or output? If you are talking about format of the date, I suggest
> >> you read
> >> http://www.karaszi.com/SQLServer/info_datetime.asp
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> >> news:43049B5F-3F12-43E4-9FA3-BBD860912772@.microsoft.com...
> >> >I have a database that is used priamrily by our UK division. The server
> >> > default setting is US time date format. Is there a way to set the UK
> >> > database
> >> > to be their date format when ours is US
> >>
> >>
> >>
>
>|||Did you check the article I posted? The client application determines the formatting, SQL Server has
no control of this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:6BDFD13D-110B-478F-B7CE-4CA295352B1A@.microsoft.com...
> So if I want the date format from any app pulling the date from the database.
> If we set the OS itself to be a certain date format, then would it always
> display in that format
> "Kalen Delaney" wrote:
>> As per the article Tibor referenced:
>> Date and time formats
>> A common misconception is that SQL Server stores these datatypes in some
>> particular readable format. That is not the case. SQL Server stores these
>> values in an internal format (two integers). However, when you use T-SQL to
>> specify a value (in an INSERT statement, for instance) you express it as a
>> string. And there are rules for how different datetime string formats are
>> interpreted by SQL Server. But note that SQL Server does not remember the
>> format in any way.
>> You cannot store your date in any format. The stored value is always
>> location independent and totally unambiguous. Format is only for display and
>> is determined when you retrieve a datetime value from a table.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "DBA" <DBA@.discussions.microsoft.com> wrote in message
>> news:00E8FB51-CE79-4BDC-BADA-2B42C4A79EF4@.microsoft.com...
>> >I actually wnat it to be stored in the UK Date Format. Can I do this at a
>> > database level or server level
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Input or output? If you are talking about format of the date, I suggest
>> >> you read
>> >> http://www.karaszi.com/SQLServer/info_datetime.asp
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "DBA" <DBA@.discussions.microsoft.com> wrote in message
>> >> news:43049B5F-3F12-43E4-9FA3-BBD860912772@.microsoft.com...
>> >> >I have a database that is used priamrily by our UK division. The server
>> >> > default setting is US time date format. Is there a way to set the UK
>> >> > database
>> >> > to be their date format when ours is US
>> >>
>> >>
>> >>
>>

Date Format

Can anyone tell me how to get SQL Server to store the date as dd/MM/yyyy,
rather than the default MM/dd/yyyy.
Thanks in anticipation,
Paul BeckettPaul,
The date is stored in neither format, but as a binary number. What you are
seeing is the way the date is interpreted to and from a character string.
This is affected by the language and locale that you run with. See SET
LANGUAGE.
Also, note Aaron Bertrand's message just a little earlier in this group,
which I quote in part: " I use the ISO standard, unambiguous, and SQL
Server safe YYYYMMDD format."
If you want SQL Server to output a string format that is different from your
machine settings, examine the CONVERT command, which has several formatting
options for dates.
Russell Fields
"Paul Beckett" <paul_beckett@.lineone.net> wrote in message
news:c15tsk$ikf$1@.titan.btinternet.com...
> Can anyone tell me how to get SQL Server to store the date as dd/MM/yyyy,
> rather than the default MM/dd/yyyy.
> Thanks in anticipation,
> Paul Beckett
>|||SQLServer doesn't store formatting information with a DATETIME or
SMALLDATETIME column. The date is actually represented internally as two
integers but it is the job of the client application to pass dates to the
server in a valid format and then to format the output for display.
When passing dates to a DATETIME/SMALLDATETIME column in SQLServer 2000, use
one of the following string formats:
'20031231' -- Just the date
'2003-12-31T17:59:00' -- Date/hours/minutes/seconds
'2003-12-31T17:59:00.000' -- Date/hours/minutes/seconds/milliseconds
these are guaranteed to work regardless of regional date format settings on
the server. Although it's possible to use SET DATEFORMAT to allow other
formats it's safer to stick to one of the standard alternatives shown above.
Format the date for display at the client side. Or use CONVERT(VARCHAR, ...,
103) to turn your dates into a VARCHAR if you must do it at the server.
--
David Portas
SQL Server MVP
--

Date Format

Hi,
I have a datetime field in my table and whose values are stored as "2004-07-29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JPhi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP

Date Format

Anyone can tell me what should I do to change the default
SQL date format from (MM/DD/YY) to (DD/MM/YY)?
Anything that I can do in the server side without touching
the client side coding.
I have another Problem that my SQL server is hosting more
than 1 database and some of the database want the date
format to be in (MM/DD/YY).
My problem is without affecting other database date
format, how should I input a new database with data format
(DD/MM/YY)?
Thanks a lot.Bee,
I don't think you can do precisely what you want.
The language ( which includes dateformat ) is associated with a login, not a database.
So if you can have a specific user requiring a different date format then
sp_defaultlanguage or Enterprise Manager may do this for you.
Otherwise at run time you can either:
set dateformat or set language
or as I do always use CONVERT with a named style, so that I don't care what
date format the server is set to.
HTH
AJ
"Bee" <ngebw@.hotmail.com> wrote in message news:033101c34f3b$e0dd2540$a601280a@.phx.gbl...
> Anyone can tell me what should I do to change the default
> SQL date format from (MM/DD/YY) to (DD/MM/YY)?
> Anything that I can do in the server side without touching
> the client side coding.
> I have another Problem that my SQL server is hosting more
> than 1 database and some of the database want the date
> format to be in (MM/DD/YY).
> My problem is without affecting other database date
> format, how should I input a new database with data format
> (DD/MM/YY)?
> Thanks a lot.

Date Format

I have a field with a datetime data type on it. I want
to run a query and return the datetime field in this
format (ex 21-JAN-2004). How can I do this?select replace(CONVERT(CHAR(11),GETDATE(),113),' ','-')
"Vic" <vduran@.specpro-inc.com> wrote in message
news:19be901c44d7c$049edca0$a501280a@.phx.gbl...
> I have a field with a datetime data type on it. I want
> to run a query and return the datetime field in this
> format (ex 21-JAN-2004). How can I do this?|||This worked!!! What does the 113 mean?
>--Original Message--
>select replace(CONVERT(CHAR(11),GETDATE(),113),' ','-')
>
>"Vic" <vduran@.specpro-inc.com> wrote in message
>news:19be901c44d7c$049edca0$a501280a@.phx.gbl...
>> I have a field with a datetime data type on it. I want
>> to run a query and return the datetime field in this
>> format (ex 21-JAN-2004). How can I do this?
>
>.
>|||Hi,
I have something close to it...it will return data as 21
Jan 2004 (without the dashes):
select convert(char(11), Column_name, 106) as Column_header
from Your-Table_Name
You can test this out and try different formats. Look up
cast and convert function in BOL.
hth
DeeJay
>--Original Message--
>I have a field with a datetime data type on it. I want
>to run a query and return the datetime field in this
>format (ex 21-JAN-2004). How can I do this?
>.
>|||Read the documentation on Books Online for CONVERT and you'll find the different style codes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vic" <vduran@.specpro-inc.com> wrote in message news:19b6601c44d9b$41f93fe0$a301280a@.phx.gbl...
> This worked!!! What does the 113 mean?
> >--Original Message--
> >select replace(CONVERT(CHAR(11),GETDATE(),113),' ','-')
> >
> >
> >"Vic" <vduran@.specpro-inc.com> wrote in message
> >news:19be901c44d7c$049edca0$a501280a@.phx.gbl...
> >> I have a field with a datetime data type on it. I want
> >> to run a query and return the datetime field in this
> >> format (ex 21-JAN-2004). How can I do this?
> >
> >
> >.
> >

Date Format

Im running an ASP.net application on a windows server with SQL server for
the database. The date format I am using is MM/DD/YYYY but that seems to
work on my local server and not on my production server. On my production
server, I get errors if the date isnt in DD/MM/YYYY. Does anyone know why or
where there is a setting to change this? I am not sure if this is a SQL or
server issueSounds a little odd. For some reason your default date format sound like UK English
Have you tried doing direct selects from the SQL sever using the MM/DD/YYYY format
if that fails then look at sp_configure for the default full-text language. Make sure that it is 1033 for US English and not 2057 for UK English. That may not be the fix but I am still looking
Jeff

Date format

Hi,

I have a problem. I have a date field in which the month always displays as 00 wether I format as dd/mm/yy or mm/dd/yy ?

I also need to know how to format as longdate. Can any one help me with this?

try capitol MM

MM/dd/yy

|||

In the format property of the textbox add "d". Sounds weird but works. You can find more on date formatting in the foll post

http://technoblab.blogspot.com

..............................................................................

Please mark as "Answer" on the post that helped you

Date format

I want to show the date in mm/dd/yy date format. In the formatdatetime function I can only specify the long date / Short date. If I am specifying short date it is taking from the system regional settings [In the regional settings it is specified as mm/dd/yyyy]. I want to show mm/dd/yy only to this report and mm/dd/yyyy to other reports. Can you please let me know how to do this?

Thanks

Where? Can you post some code?

|||

I am using the following code to show the date in my SQL Server reportig services report,

FormatDateTime(Fields!Date.Value,2). It is showing mm/dd/yyyy format. I want to show in mm/dd/yy format. This mm/dd/yy format is only for one report. I want to show mm/dd/yyyy for the other reports.

|||

See if this helps:

=Format(Fields!Date.Value,"MM/dd/yy") ' this is case sensitive

Got it from your other post:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2372543&SiteID=1

|||

I got the answer. I have to use the following,

Format(Fields!YourDateField.Value,"MM/dd/yy")

|||

Thank you. This is working

Date Format

Hi everyone, I need help with the date formatting in ssrs 2000. I'm writting reports with date ranges, specific dates, and all are working fine. Now, I was asked to create a report that shows current month, a different one for the QTR, and and also for the current year. I've tried different formats, keep gettin stuck where it doesnt return data. Please help.

Abner

Are you using stored procedure? where do you need a help? in SQL or Report designer, please give me some more details.

|||

I need help in report designer. I was able to run my report and get current dates, GETDATE(), but when it comes to current month, I've tried different ways but it dont work. So, I'm seeking as much help as possible. I'm still new at reporting service, but eager to learn as much as possible.

Thanks advance

Abner

|||

Hi, Abner:

IDoes this meet your needs?

Month(ToDay())

Year(ToDay())

|||

Hi REX,

Thanks for the reply, but I have tried those formats and i just get a blank page. Well, when I type MONTH(TODAY()) it dont reconize today, so I wrote MONTH(GETDATE()), and it just returns a blank page...ive tried it with =DatePart(m, GETDATE()), also =DATEADD(m, 0, GETDATE()), and DatePart("m", field!Promise_Date.Value). and it still just give me a blank page. Is it because I also have it divided by week? even thought I test it without breaking it into weeks. Pleasssssssssseeeeeeeeee help. I dont know how else to do it.

Abner

|||

Hi Abner,

Date related functions in report viewer are not same as we have in dot net, they have some weired behavior, so we cant not use abbreviated Dateparts like "mm", "m" or "d". Check this post for more info on this -

http://blogs.msdn.com/bimusings/archive/2005/09/13/464836.aspx

Date format

I'm trying to build a file-name variable that begins with a 'date'.

For example, I need to build logic in a T-SQL script that will access a file called 08032006abc123, with '08032006' being a representation of the date 8/3/2006.

Is there a quick/easy way to create this string, based on the date 8/3/2006? (would be nice if there was a date function that would zero-fill)

If you can use the date format YYYYMMDD or YYMMDD you can use CONVERT to get the encoded date ( http://msdn2.microsoft.com/en-us/library/ms187928.aspx ) else you can always do this and then rearrange the out string using the SUBSTR|||

The following syntax will return the date in the manner you are looking for:

DECLARE @.date datetime

SET @.date = GETDATE()

select replace(convert(varchar(10), @.date, 101), '/', '')

Results: 09082006

Date Format

Hi everyone.

i have a big database with many stored procedures. i need way that allow me to get date from sql server 2000 by two format(MM/DD/YYYY, YYYY/MM/DD). and if i can do that with minimum effort. i wondering if i can change the come data from sql by using any objects.

any help please.

You could use SET DATEFORMAT. For example:

Code Snippet

SET DATEFORMAT mdy;

|||

yah thanks. i want to change it for all stored procedures from one place. and i want to send the format that i need to return like this

dont need to send it by each call how i can determine it from one place.

|||

As we see in Books Online the sintax is :

SET DATEFORMAT {format | @.format_var}

so, @.format_var can get value from a table and with your application you can modify dynamically the value in the record of table

|||

ok thanks but i need change it from one place for whole date return from sql. i have many procedures and the change is big.

if i can change it from one place applying to all procedures.

|||

Nope. It is not possible. SQL Server by default return the date values in "YYYY-MM-DD hh:mi Tongue Tied s.ms". You can't override this format.The retriving format only changed once you convert the datetime values into character string using convert/cast.

You have to do something in your UI.

Date Format

Hi,

I want my query should return a content of date field in 'MMM-YYYY' format.

Can Anyone help me to get this

You may use Year() and Month() functions alongside the convert() function.|||

There is no built in function to do that. You can use the date functions like datename to get theappropiate results.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Date Format

Hi,

I'm conditional split and I want to get the data base on getdate with a format: Here's the scenario:

I hava a column datecreated with a default value of getdate() format

I want to convert the datecreated format like (ex 01/01/2007) ==

getdate with a format also like '01/01/2007"

Can anyone help with this, at least a sample

also I tried to use this

(DT_WSTR,2)DATEPART("MM",DateCreated) + "/" + (DT_WSTR,2)DATEPART("DD",DateCreated) + "/" + (DT_WSTR,4)DATEPART("YYYY",DateCreated) == (DT_WSTR,2)DATEPART("MM",GETDATE()) + "/" + (DT_WSTR,2)DATEPART("DD",GETDATE()) + "/" + (DT_WSTR,4)DATEPART("YYYY",GETDATE())

But I still don't get the right record

Thanks

The expression you are using to create the date string from GETDATE() will result in strings such as 1/1/2007, but it looks like you want to compare that to 01/01/2007. You can use the following modification to the right hand side of your comparison to pad the month and day with leading zeros:

RIGHT("0" + (DT_WSTR,2)DATEPART("MM",GETDATE()), 2) + "/" + RIGHT("0" + (DT_WSTR,2)DATEPART("DD",GETDATE()),2) + "/" + (DT_WSTR,4)DATEPART("YYYY",GETDATE())

Depending on how the datecreated column is formatted, you may or may not want to do something similar on the left side of your equation.

Let me know if this solves the problem for you.

Thanks
Mark

|||I don't understand. If both formats are timestamp formats, they you shouldn't have an issue comparing dates. Dates aren't also strings, so treating them as such doesn't make a whole heck of a lot of sense.

The issue with getdate() is that it carries with it the time component as well. Perhaps you need to cast your date fields to (DT_DBDATE) first, before comparisons.|||(DT_DBDATE)[DateCreated] == (DT_DBDATE)getdate()|||

Phil Brammer wrote:
(DT_DBDATE)[DateCreated] == (DT_DBDATE)getdate()

Hi Phil,

Thanks for the reply I'm going to check this one...

Date format

How do I get the following date format 2005-01-31 to be 01/31/2005?
SELECT
convert(datetime,Convert(varchar(10),getdate(),20) ,110) as Dateselect convert(varchar,getdate(),101) ?

date format

The date is stored in my DB as a numeric 9(18,9) data type .How can i convert that value into regular data type
HEre are the two dates in my DB.How can i change this into regular data formart.
37774.98856
37728.38941
Thanks.What is it suppose to be?

How about

SELECT CONVERT(datetime,37774.98856)

date format

I know this is an easy question but I am trying to get a date in the format dd/mm/yyyy. In oracle it would be tochar('12/31/2001','dd/mm/yyyy) what is it in sql server?convert(char(10), '12/31/2001', 101)

Date format

In SQL-Server 2000, the supported date format is :
2003-12-30 03:47:34:000

In DB2 and SQL Anywhere Sybase, the date format is :
2003-12-30.03.47.34.00000

I need to import/export data from text files that contain dates in the DB2 format.

Is there a way to change the supported date format in SQL Server 2000 ?
Can I change the default format in the DB-Library ?

No hope but tkx anywaySQL Server default format is actually 2003-12-30 03:47:34.000

We import date from other DBMS as string and then convert it to sql datetime format. In your case it would be as of the following,

select convert(datetime, left(replace(stuff('2003-12-30.03.47.34.00000', 11, 1, ' '), '.', ':'), 19))|||well, you didn't quite answer my question
but it made me think that i could simply
change the Datetime columns of my tables
into Varchar types

Hell with dates !

Thanks !|||If you change your datetime values to varchar, you are just inviting greater problems in the future. Import your data correctly the first time.

You may need to import it first as a string and then convert it (using joejcheng's formula), but you should store your final values in datetime format.

blindman|||The users of my program download files every day and
the program loads them into the database to update their data.
These files come from a DB2 database...

- AND -

Each time they shut down the program,
a file is generated with the latest data updated.

That's why I'm so concerned about the dateformat.|||How does the program load the data? Does it use bulk copy, or does it execute insert statements?

blindman|||You'll need to convert the unload from DB2 and format on the way out...

Yes and varchar is fine

What platform, mainfram, NT, UNIX?

And is ti fixed width (ah, a mainframe canidate) or csv?|||At this moment we're using win98 platform but
next year we'll migrate the prog on winXP.

We use fixed columns files|||We're on Sybase (SQL Anyware) database but
next year we're chaging the database to SQL Server
(boss wants to save money).

I'm studying the impacts and looking for
the smoothest way to change the database-progVB
dialog.|||That's right !

The program imports/exports dates from and to DB2.

So with all you're advices, I'll :

- declare my tables with Char(26) instead of Datetime or VarChar to be sure that I have fixed columns
- even maybe create a user define type named DB2_DateTime with rules on data to be stored in those columns
- use the BlindMan function "CurrentTimestamp" to replace my "Current timestamp" in the old SQL Anywhere queries to store the Now date in the DB2 format
- use bulk insert to import data from a DB2 file (DB2 dates)
- maybe use bcp to export data into a file at DB2 destination

Getting there !
And they say that USA does'nt like France !

Date Format

How can you maintain consistance in generating output for a DATE datatype?
example, JAN, when CAST as a varchar it becomes 1, NOT 01Use function CONVERT instead.
Example:
select convert(varchar, getdate(), 126)
AMB
"Jim Yurt" wrote:

> How can you maintain consistance in generating output for a DATE datatype?
> example, JAN, when CAST as a varchar it becomes 1, NOT 01|||Can you elaborate a bit more? Are you saying that you want a particular outp
ut string format which
isn't available in the 3:rd parameter to the CONVERT function? Or do you wan
t to convert into a
string format which SQL Server later will be able to convert into datetime s
afely regardless of
language settings?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
news:A2EF5257-1A59-4C55-ADAD-2EAE71CB6ED8@.microsoft.com...
> How can you maintain consistance in generating output for a DATE datatype?
> example, JAN, when CAST as a varchar it becomes 1, NOT 01|||if I do something like this;
ELSE(Month(Convert(varchar, DATEFIELD, 126)))
for months up to October (10th) month, my output is 1 char wide, I would
like the 0 as a place holder, so to speak...
jan = 01 NOT 1
feb = 02 NOT 2...
need fixed format for output
"Tibor Karaszi" wrote:

> Can you elaborate a bit more? Are you saying that you want a particular ou
tput string format which
> isn't available in the 3:rd parameter to the CONVERT function? Or do you w
ant to convert into a
> string format which SQL Server later will be able to convert into datetime
safely regardless of
> language settings?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
> news:A2EF5257-1A59-4C55-ADAD-2EAE71CB6ED8@.microsoft.com...
>
>|||> ELSE(Month(Convert(varchar, DATEFIELD, 126)))
Is above the code you are actually executing? Only specifying varchar, witho
ut length, is same as
saying varchar(1), except in CAST and CONVERT where it defaults to 30 (for s
ome strange reason).
Anyhow, always specify the length.
Also, what datatype s DATEFIELD of? Datetime? If so, no need for the CONVERT
function at all.
Your problem is that the Month function returns an integer, not a string. Th
e number 01 and 1 is the
same number. We still don't know what you are doing with the number returned
from the expression. Is
it to be compared to a string in which you have leading zeroes? If so, why d
o you have those numbers
in a string datatype?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
news:7A4CE644-C87E-443E-8E49-7349038BCF99@.microsoft.com...
> if I do something like this;
> ELSE(Month(Convert(varchar, DATEFIELD, 126)))
> for months up to October (10th) month, my output is 1 char wide, I would
> like the 0 as a place holder, so to speak...
> jan = 01 NOT 1
> feb = 02 NOT 2...
> need fixed format for output
> "Tibor Karaszi" wrote:
>|||DATEFIELD (for the example) is datetime (datatype)...and to your point, MONT
H
return an integer, that's what I don't want.
I need a way to return the CHAR value of the MONTH from a datetime field...
"Tibor Karaszi" wrote:

> Is above the code you are actually executing? Only specifying varchar, wit
hout length, is same as
> saying varchar(1), except in CAST and CONVERT where it defaults to 30 (for
some strange reason).
> Anyhow, always specify the length.
> Also, what datatype s DATEFIELD of? Datetime? If so, no need for the CONVE
RT function at all.
> Your problem is that the Month function returns an integer, not a string.
The number 01 and 1 is the
> same number. We still don't know what you are doing with the number return
ed from the expression. Is
> it to be compared to a string in which you have leading zeroes? If so, why
do you have those numbers
> in a string datatype?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
> news:7A4CE644-C87E-443E-8E49-7349038BCF99@.microsoft.com...
>
>|||Allow me to rephrase your requirement - return the month of a datetime value
as a char(2) value with leading zeros. When you phrase it that way (and
when it exactly defines what you want), the solution becomes much easier to
find.
select convert(char(2), getdate(), 101)
"Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
news:2159CCA0-3841-4436-8356-782E18E28821@.microsoft.com...
> DATEFIELD (for the example) is datetime (datatype)...and to your point,
MONTH
> return an integer, that's what I don't want.
> I need a way to return the CHAR value of the MONTH from a datetime
field...
> "Tibor Karaszi" wrote:
>
without length, is same as
(for some strange reason).
CONVERT function at all.
string. The number 01 and 1 is the
returned from the expression. Is
why do you have those numbers
would
particular output string format
you want to convert into a
datetime safely regardless of
datatype?|||Thanks for making more sense of my 'query'...
indeed what I was looking for was something like this;
convert(char(2), DATEFIELD, 101) + convert(char(2), DATEFIELD, 103) +
convert(char(4), DATEFILED, 126)
which was much better than this;
replace(replace(substring(convert(varcha
r(15), DATEFIELD, 126),5,3) +
substring(convert(varchar(15), DATEFIELD, 126),9,3) +
substring(convert(varchar(15), DATEFIELD, 126),1,4), 'T', ''), '-', '')
thanks for the help everyone!
"Scott Morris" wrote:

> Allow me to rephrase your requirement - return the month of a datetime val
ue
> as a char(2) value with leading zeros. When you phrase it that way (and
> when it exactly defines what you want), the solution becomes much easier t
o
> find.
> select convert(char(2), getdate(), 101)
> "Jim Yurt" <jyurt@.odh.oh.gov> wrote in message
> news:2159CCA0-3841-4436-8356-782E18E28821@.microsoft.com...
> MONTH
> field...
> without length, is same as
> (for some strange reason).
> CONVERT function at all.
> string. The number 01 and 1 is the
> returned from the expression. Is
> why do you have those numbers
> would
> particular output string format
> you want to convert into a
> datetime safely regardless of
> datatype?
>
>|||You will get same result with:
select replace(convert(varchar(10), DATEFIELD, 101), '/', '')
go
AMB
"Jim Yurt" wrote:
> Thanks for making more sense of my 'query'...
> indeed what I was looking for was something like this;
>
> convert(char(2), DATEFIELD, 101) + convert(char(2), DATEFIELD, 103) +
> convert(char(4), DATEFILED, 126)
> which was much better than this;
>
> replace(replace(substring(convert(varcha
r(15), DATEFIELD, 126),5,3) +
> substring(convert(varchar(15), DATEFIELD, 126),9,3) +
> substring(convert(varchar(15), DATEFIELD, 126),1,4), 'T', ''), '-', '')
> thanks for the help everyone!
> "Scott Morris" wrote:
>|||And a last comment - formatting of datetime values is best done on the
client (where language/regional issues are more appropriately handled).
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:01094609-D7E9-4C70-9AB6-A2DB89157AE5@.microsoft.com...
> You will get same result with:
> select replace(convert(varchar(10), DATEFIELD, 101), '/', '')
> go
>
> AMB
> "Jim Yurt" wrote:
>
value
(and
easier to
point,
varchar,
30
the
number
so,
I
Or do
into
DATE

Date Format

How to format a date that looks like this yyyy-mm-dd in a table to mm-dd-yyy
y
in a view? What is the syntax?
Thanks
--
TSCONVERT(CHAR(10), ColumnName, 110)
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:8B5B0627-00E9-4EFF-A23A-1EE1C03A85A9@.microsoft.com...
> How to format a date that looks like this yyyy-mm-dd in a table to
> mm-dd-yyyy
> in a view? What is the syntax?
> Thanks
> --
> TS|||Hi,
Use CONVERT funtion.
select convert(char(20),getdate(),101)
For a view use:-
Create view V1 as Select convert(char(20),column_name,101) AS Some_name from
Table_name
Thanks
Hari
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:8B5B0627-00E9-4EFF-A23A-1EE1C03A85A9@.microsoft.com...
> How to format a date that looks like this yyyy-mm-dd in a table to
> mm-dd-yyyy
> in a view? What is the syntax?
> Thanks
> --
> TS|||Just to add some to the correct answers posted. SQL Server does not store
datetimes in any format that you would see as a datetime. It is two
Integers. Format is the result of the gui that you are displaying it in
given no other formatting such as CONVERT. See these for more details:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
Andrew J. Kelly SQL MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:8B5B0627-00E9-4EFF-A23A-1EE1C03A85A9@.microsoft.com...
> How to format a date that looks like this yyyy-mm-dd in a table to
> mm-dd-yyyy
> in a view? What is the syntax?
> Thanks
> --
> TS|||To prevent a 'next time'... ;)
http://msdn.microsoft.com/library/d...br />
2f3o.asp
ML|||Right, THIS will prevent a next time :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BCCDC6A4-FB90-46DD-9AD4-00D973CC59B6@.microsoft.com...
> To prevent a 'next time'... ;)
> http://msdn.microsoft.com/library/d... />
o_2f3o.asp
>
> ML

Date Format

hello

I'm having problem with date format.I have CRYSTAL 9 and using CRYSTAL ENTERPRISE 9 to view reports.
default system date format is dd/mm/yyyy.when the report is viewed with CR, the date format is system date format and this is what i want.but when viewed by networked users using CE some are veiwing date as 'mm/dd/yyyy'.even if user date setting is 'dd/mm/yyyy' and server setting is 'dd/mm/yyyy'.

Please advice
RegardsHi,

Could you use a fixed format for your date fields? This would be the simplest solution. Personally I'm working an environment with several regional settings and I have defined date layout parameters in my date base. I fetch the data in a subreport into shared variables in the report header and then I use the variables to define the date format. This works fine.

- Jukka

Date Format

Hi,
I have a datetime field in my table and whose values are stored as "2004-07-
29 00:00:00.000".
I am trying to write a sql statement in query analizer to bring up date as "
29/07/2004".
Can anybody please help me.
My sql statement is like this...
select convert(datetime,from_date) from table1.
Thanks in Advance.
JPhi jp,
use convert function and, convert it to varchar with the formatting number.
in your case it will be 103
Ex:
select convert(Varchar(10), getdate(), 103) 'dt'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
"JP" <john_p@.yahoo.com> wrote in message
news:872CB9EB-188E-43D8-B313-E7160B1E7C84@.microsoft.com...
> Hi,
> I have a datetime field in my table and whose values are stored as
"2004-07-29 00:00:00.000".
> I am trying to write a sql statement in query analizer to bring up date as
"29/07/2004".
> Can anybody please help me.
> My sql statement is like this...
> select convert(datetime,from_date) from table1.
> Thanks in Advance.
> JP

Date Format

date format

How can I strip the time from datetime field without converting to Char or
Vchar?You can't. DATETIME and SMALLDATETIME always store both date and time.
What you can do is set the time to midnight. For example:
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMES
TAMP),0)
David Portas
SQL Server MVP
--|||Date already exists so I am not sure how to utlize
SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMES
TAMP),0)
to the existing date field.
"David Portas" wrote:

> You can't. DATETIME and SMALLDATETIME always store both date and time.
> What you can do is set the time to midnight. For example:
> SELECT DATEADD(DAY,DATEDIFF(DAY,0,CURRENT_TIMES
TAMP),0)
> --
> David Portas
> SQL Server MVP
> --
>
>

Date format

Hello
I have a table that one of its column is in dateTime
format.
My date format in the pc is dd/mm/yyyy (in the Regional
Options)
In the Query Analyzer I run "SET DATEFORMAT DMY" but when
I try to enter data as "21/3/2004 12:00:00" I get error.
when I try to enter data as "3/21/2004 12:00:00" I get no
error and the data is entered into the table.
I need to work only with "dd/mm/yyyy hh:mi:ss" format.
How can I config/set my SQL Server to work
with "dd/mm/yyyy hh:mi:ss" format ?
Thank you very much!!!Hi,
This worked fine for me, Please check.
create table date_chk(x datetime)
go
set dateformat 'dmy'
insert into date_chk values('21/3/2004 12:00:00')
Thanks
Hari
MCDBA
"MR.T" <anonymous@.discussions.microsoft.com> wrote in message
news:1562e01c41af3$070994a0$a601280a@.phx
.gbl...
> Hello
> I have a table that one of its column is in dateTime
> format.
> My date format in the pc is dd/mm/yyyy (in the Regional
> Options)
> In the Query Analyzer I run "SET DATEFORMAT DMY" but when
> I try to enter data as "21/3/2004 12:00:00" I get error.
> when I try to enter data as "3/21/2004 12:00:00" I get no
> error and the data is entered into the table.
> I need to work only with "dd/mm/yyyy hh:mi:ss" format.
> How can I config/set my SQL Server to work
> with "dd/mm/yyyy hh:mi:ss" format ?
> Thank you very much!!!
>|||This is a prob to do with US/ UK date formats in SQL... you need to use the
"SET DATEFORMAT DMY" in SQL where it inserts the date into the DB, for examp
le...
var sql_statement = "SET DATEFORMAT DMY \n UPDATE Mobile..."
\n refers to a line break, for javascript. If you use VBscript, replace with
appropriate command vbctrlf or something (not sure exact).
This should be done on all SQL statements that are entering dates.
Hope it helps,
Chris
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Date Format

Im running an ASP.net application on a windows server with SQL server for
the database. The date format I am using is MM/DD/YYYY but that seems to
work on my local server and not on my production server. On my production
server, I get errors if the date isnt in DD/MM/YYYY. Does anyone know why or
where there is a setting to change this? I am not sure if this is a SQL or
server issueSounds a little odd. For some reason your default date format sound like UK
English.
Have you tried doing direct selects from the SQL sever using the MM/DD/YYYY
format.
if that fails then look at sp_configure for the default full-text language.
Make sure that it is 1033 for US English and not 2057 for UK English. That
may not be the fix but I am still looking.
Jeff

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
GunSee my reply in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.g
bl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>|||Hi
Read more on convert/cast functions in sql bol
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gunawan" <jgun98.milis@.gmail.com> wrote in message
news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

date format

Hello
I m using a database with MS SQL SERVER 2000, in which I insert
SMALLDATETIME values.While using the query browser, with the option "Use
local setting for display date, money" unchecked , I get results like :
2005-09-24 00:00:00

With the option checked : 24/sept./05

I get access to the DB with PHP scripts and I only get values like
"24/sept./05", but I d like to get SMALLDATETIME style values. How could I
do this ?

thanks in advanceDatetime values are stored in a binary format, so you have to decide
how they should be formatted when you retrieve the data. Although you
can use CONVERT() in a query to get a specific format (see Books
Online), it's usually a better idea to do the formatting on the client
- presentation and formatting are really client-side tasks, and it's
easier to handle different client locales etc. See here for more
information about working with datetime data:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||Yes, I understand well what you explain, and this is because I want to
format on the client side that I want the "raw" values. But, in PHP (or
maybe the problem comes from elsewhere, I don't know), the values is
automatically transformed following the local & area settings.("09"
become => "september" or "septembre" or "septembria" or anything else
following the local language )

I didn't found the way to do the same that in "Query Browser , while not
checking the option " use local settings to translate datetime, money
values", so I used the CONVERT() function, but for my usage, it s just
compute time that could have be saved if it was possible to get "raw" data.

I think it s even better than parse and transform the string
"25/sept./05" that I get with PHP function. This script will be used in
several servers and I can't trust the local settings (country ,
languages) of these servers, since, "sept" could be anything else in
other languages.

Finally, I found that it s very very annoying that it is not possible to
get raw values, without having then translated or converted automatically.

Thanks you very much for your answer.

date format

where and how can I set a system level date format for all reports?
meaning that format will be the default for all reports unless coded
otherwise
thnxThe date format is obtained from the client computers which are running the
reports. Their settings can be accessed from the Regional and Language
Options in the Control Panel. So, you really don't have absolute control
over your date format unless you explicitly state the format in the report.
"chriske911" wrote:
> where and how can I set a system level date format for all reports?
> meaning that format will be the default for all reports unless coded
> otherwise
> thnx
>
>

Date Format

How can I format my date within Reporting Services to display like this:
Monday, January 9, 2006
instead of like this: 1/9/2006 12:00:00 AMNevermind, I found the answer. The answer is put a "D" to show the long date
in the format property.
"David" wrote:
> How can I format my date within Reporting Services to display like this:
> Monday, January 9, 2006
> instead of like this: 1/9/2006 12:00:00 AM|||Hi
I need to know how to display current date in the report.
Do i need to do something with rdl.
I wrote one JScript function there for the display of the date but its not
working.
As I am using xslt also at the render of the report for the foprmatting
purposes, so I wrote the same Jscript there also but still it is not working.
Can u please guide me.
Thanks in advance.
--
Vishnu
"David" wrote:
> Nevermind, I found the answer. The answer is put a "D" to show the long date
> in the format property.
> "David" wrote:
> > How can I format my date within Reporting Services to display like this:
> >
> > Monday, January 9, 2006
> >
> > instead of like this: 1/9/2006 12:00:00 AM

Date Format

Hi,
I have a report which display the data in a dataset and one of the column is
date type. The date format return by the dataset is in mm/dd/yyyy format,
and we have no rights to change the stored procedure which returning the
dataset.
I am not sure whether we can do some date formatting within the report
itself, with some scripting or formula.
Really appreciate your help.
ThanksBy Default, SQL RS has the flexibility to take the parameter as
DateString. That is from parameter side.
if you want to show the field formatted,Go to dataset properties >
fields > (here you can see all the fields) > add one field .Give the
VBExpression you want to play with.
after that run the dataset to preview
Hope this helps
Thank you
Raj Deep.A
On Apr 23, 4:19=A0pm, "Thalia Mei" <thalia...@.gmail.com> wrote:
> Hi,
> I have a report which display the data in a dataset and one of the column =is
> date type. The date format return by the dataset is in mm/dd/yyyy format,
> and we have no rights to change the stored procedure which returning the
> dataset.
> I am not sure whether we can do some date formatting within the report
> itself, with some scripting or formula.
> Really appreciate your help.
> Thanks

Date Format

My date is being returned as mm/dd/yyyy - how do I format this to be
dd/mm/yyyy. My region setting are UK.
ThanksSome of the region settings didnt work correctly in the initial version
of Sql Server 2000 Reporting Services. "I think" that they were fixed
in either SP-1 or SP-2.
However, as a temporary work around you can explicitly set the date
format on each report item by selecting the table-cell, textbox, or
field you want to format and going to Properties to set the "Format"
property. The format string should simply be the date format you
prefer such as "DD/mm/yyyy" (without quotes).
Hope this helps,
Lance Hunt
http://www.lance-hunt.net/|||"Mike R" <news@.mikeread.freeserve.co.uk> wrote in message
news:d99959$3lb$1$8302bc10@.news.demon.co.uk...
> My date is being returned as mm/dd/yyyy - how do I format this to be
> dd/mm/yyyy. My region setting are UK.
> Thanks
>
Found my solution..
http://www.eggheadcafe.com/articles/20040823.asp|||"Lance" <lancehunt@.gmail.com> wrote in message
news:1119365398.406661.173780@.g43g2000cwa.googlegroups.com...
> Some of the region settings didnt work correctly in the initial version
> of Sql Server 2000 Reporting Services. "I think" that they were fixed
> in either SP-1 or SP-2.
> However, as a temporary work around you can explicitly set the date
> format on each report item by selecting the table-cell, textbox, or
> field you want to format and going to Properties to set the "Format"
> property. The format string should simply be the date format you
> prefer such as "DD/mm/yyyy" (without quotes).
> Hope this helps,
> Lance Hunt
> http://www.lance-hunt.net/
>
Thanks Lance, If found this http://www.eggheadcafe.com/articles/20040823.asp
when resolves my issue.
Kind Regards
Mike

Date Filtering vs Row placement misbehaviour

I am using the classic Add Business Intelligence date dimension hierachy and getting correct results when I had my calendar hierachy on the rows expanded to the day level and my dimension calculations on the columns. The results were wrong when I moved the calendar hierachy to the filter area and selected one date.

In particular, the [Date].[Date Calculations].[Current Member Name] = [Date].[Reporting Calendar].CurrentMember.Name shows that when you filter the Hierachy down to the day, the Current Member is always incorrectly showing the week (in every cell). It is correct when filtered to all other higher levels than date (week, month...).

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Anyone seen anything like this - filtering by a Hierachy member does not work at the lowest level (the day level) but it does work as a row heading? It may be related to the lowest level being the Dimension Key?

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Are we missing somehting when we set a 'conformed' hierarchy? I did not have the lowest level as the dimesion key. Then I set it to the DImension key for my 11 hierarchies in the Product dimension. It worked for a small sample data set and then it started failing when I introduced it to the main project with 10+ dimensions and many hierarchies.

Let me have your views on this.

|||

I looked at your issue. It is not obvious to me that the problem I had with .CurrentMember being wrong under a lowest level filter member is the same as your TOPCOUNT problem. I had to move my formulae out to a different dimension and put them on an attribute hierachy to work.

However, by lots of trial and error with the display of simple diagnostic formulae, it is likely that you can identify a key simple value that is not correct. We need more such simple problem examples because I am convinced there is a basic structural problem in the implimentation of MDX against UDM and it will show up as the wrong answer in many different circumstances.

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

If you have defined date hierarchies such as Fiscal or calendar, have you ensured that the key element of the attributes used in the hierarchy is unique? You might have to use the key collection to define a combined key of Year, Quarter for the quarter in the attribute hierarchy.

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Also I have repeated the problem in any other dimension hierachy where the lowest level used as a filter member causes the .Currentmember to get the incorrect value (it gets the parent member of the filter value)

So it seems to be an issue on even the simplest dimension hierachy when the formulae is added to a hierachy within the filter dimension. I fixed it by moving all the formulae out to a completely different dimension.

Date Filtering vs Row placement misbehaviour

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Let me have your views on this.

|||

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Date Filter in Report Builder

The "On or Before" filter does not work with the 'On' part of the
filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
but does show 29/11/2006.
Is this a bug or maybe i'm missing something.
Regards
TobiI haven't used Report Builder that much, but its possible that Report
Builder is using the format of 30/11/2006 12:00:00 (12:00 am), and your
data from the db is 30/11/2006 09:15:00 (9:15 am).
Tobi wrote:
> The "On or Before" filter does not work with the 'On' part of the
> filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
> but does show 29/11/2006.
> Is this a bug or maybe i'm missing something.
>
> Regards
> Tobi|||I agree with Topher's assessment. If this was an actual report, I would
suggest creating an internal report parameter that adds one day and
then subtracts one second from the original parameter value. You would
then change your query parameter to use the new, internal parameter
value.
However, this is Report Builder, which I have not used much. Try to do
something like I just described.
Just had another thought... In your model, try to strip the times out.
If you aren't using them anyways, then you don't need them. If
everything has a time of 00:00:00 (12:00:00 AM), then your formula
should work just fine.
HTH,
Josh
Topher wrote:
> I haven't used Report Builder that much, but its possible that Report
> Builder is using the format of 30/11/2006 12:00:00 (12:00 am), and your
> data from the db is 30/11/2006 09:15:00 (9:15 am).
>
> Tobi wrote:
> > The "On or Before" filter does not work with the 'On' part of the
> > filter i.e. On Or Before 30/11/2006 does not show items for 30/11/2006
> > but does show 29/11/2006.
> >
> > Is this a bug or maybe i'm missing something.
> >
> >
> > Regards
> >
> > Tobi

Date filter in MDX

Hi guys,

I have a cube where I want to make filter based on date range. I've created a cube with appropriate measures and I have Dimension with date field ( I want to filter on that field).

I've found two solutions:

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= "2/6/2007" --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= "2/7/2007" --'2/7/2007'

but this stopped working after I applied SP2 - i just get null values for all measures

and this one

[Reservation Search Log].[Simple Insert Date].&[February 1, 2007] :
[Reservation Search Log].[Simple Insert Date].&[February 10, 2007]

but in I would like to pass these dates as parameters. and if both dates are outside the range I get no results. In real case I should get all rows like this
data in SSAS February 1, 2007 to February 10, 2007
I pass January 1, 2007 to March 10, 2007
and no result are returned

If there is no appropriate records for both date parameters no rows are returned...

Any ideas are highly appreciated.Filter is not really an efficient way to do this, if possible I usually try to populate drop down lists of members in reports, but sometimes this is not always practical. Have you tried converting your comparison values to dates as well?

FILTER([Reservation Search Log].[Reservation Search Log].ALLMEMBERS,
CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" ))>= CDate("2/6/2007") --'2/6/2007'
AND CDate([Reservation Search Log].[Reservation Search Log].Properties( "Simple Insert Date" )) <= CDate("2/7/2007") --'2/7/2007'|||Hi Darren,

Thank very much for helping me with this....

I don't get what do you mean with converting comparison values to dates...

the example I posted is from real MDX query in which I got that doesn't work on SP2 and both sides are converted using CDate before comparison is made

Any other ideas? What is the best way to achieve such goal?|||

The sample you posted only had one side converted to a date, the other was a string value. The example you posted has

>= "2/6/2007"

as the first comparison, which is a comparson to a string value where as if you look at what suggested in my last post it was to use something like:

>= CDATE("2/6/2007")

I tested this in SP2 and it works for me.

|||

this is interesting...

I had this filter clause in where clause and I had the (null) problem...

I moved filter clause in select in subquery and everything is fine now...

Sorry for missing the changes in your post. I've made so many tests and I thought I posted same as you

Thanks a lot, Darren

Date Filter

I have a report in Visual Studio that I want to limit to just the activity for today. Normally I would add a WHERE stmt to the sql script that says docdate=getdate(). However this does not return any data to me in VS. I have tried many different syntax. Does anyone have the correct syntax or filter value that works in Visual Studio? Thank you.

getdate() returns a datetime, not just a date, which is why your sql query is failing. For a really helpful list of date formatting in SQLServer check this url: http://www.sql-server-helper.com/tips/date-formats.aspx

You have two options for selecting your activity records:

- do it purely in sql: use getdate to get an initial date, CONVERT it to get rid of the time component and store it as your start date, use DATEADD on it to add 24 hours and store this as the end date, then use a BETWEEN in the WHERE clause

- pass a date (as a string) through from the report parameters (could be a hidden parameter with a default value of =Format(Now, "yyyy/MM/dd")), then CAST it as a datetime in sql, and compare your activity records to it in the WHERE clause.

Date fields causing screen refresh.

We have many reports where we have date parameter for a begin and end date.
When users either type a date or select it from the calender, it is causing
the screen to refresh when they are done with that field. This is very
anoyning to the users and is turning them off to RSS. Is there a setting
that is causing this?On May 11, 7:09 pm, Tom <T...@.discussions.microsoft.com> wrote:
> We have many reports where we have date parameter for a begin and end date.
> When users either type a date or select it from the calender, it is causing
> the screen to refresh when they are done with that field. This is very
> anoyning to the users and is turning them off to RSS. Is there a setting
> that is causing this?
Most likely, this is caused by one of two things:
- Either, the report parameters have default values set instead of
null (which would require the View Report button to be selected, if
null was the default). You can check this via: Layout view -> Report
tab -> Report Parameters...
- or, the Autorefresh is set (you can check this via: Layout view ->
Report tab -> Report Properties... -> General tab -> Autorefresh).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Date Fields - To Null or Not to Null

I am new to MS-SQL and I am doing some testing with converting a few of our current tables from Pervasive 2000i format. The first thing I run into is problems with data fields. We have a lot of date fields in our various tables that have no date (00/00/0000) which is correct. It could mean for instance that something has not happened yet (i.e. Posting Date).

Ok here is my question: Is it better not to allow Nulls in Date fields? If so, what is the best practice to have for the default (i.e. 01/01/1901) date?

We use Crystal Reports and a RAD tool to produce our many complex reports. So this will be a very important decision.

Thanks,

FrankMy vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.

blindman|||I agree... Go NULL all the way

Originally posted by blindman
My vote is to allow nulls. I never liked the idea of putting in a bogus date (9/9/9999) to represent a missing date value. I've always thought it was kind of sloppy.

blindman|||You have 3 options as I see it..

1) Allow Nulls
Pros: Quick
Cons: Requires 3 valued logic, not very indexable

2) Define and Document a "bogus" date as blindman called it
Pros: You actually have a value that can use an index
Cons: Documentation and logic

3) Project away the date field into a new table using a one-to-none or one-to-one constraint.
Pros: Nice and relational...
Cons: Requires an extra join.

Depending on what option you do go for, don't forget that you can setup views to help out ie: ViewValidDates (Good Dates) and ViewNonValidDates (NULLs, BogusDate etc...)|||definitely use nulls

byrmol, since when can you not index a column that has nulls?

or are you implying that the index is never used? i know i've created indexes on columns with nulls (i was on sql 7) but i never bothered to dig into the execution plan to see if the index was actually being used

the "default" date will get you into WAY more trouble than nulls

that is because the "default" date forces upon you a different "third-value logic" and therefore since you have to code for three values anyway, it is better to use nulls, since there are cases (certain sql constructions) where nulls are easier than having always to remember to add "where datefield <> 'xxxx-xx-xx'" to each and every query, which, i guarantee you, somebody (usually yourself) will forget to do

as for the third option, normalizing the date out into a separate table, just so there's no column in the main table with a null in it -- i know where you're coming from but i really don't think you get much from this, because although you can bring in the date with a join, imagine doing that for all the fields in a typical application which can go null, and suddenly you have to code twenty-seven-table-join monstrosities just to get an "original" row

rudy|||null sounds good to me....

Date Fields

Hi all, i would be really grateful if you could help with this one
I have an MS SQL table with amongst others 3 fields -
DatePosted
Dayspostedfor
ExpiryDate
The value inserted into DatePosted id GetDate()
The vaue inserted into Dayspostedfor is eith - 7/14/21 or 28
I'd like ExpiryDate to calculate the other two to create an expiry date -
for instance -
DatePosted 01/01/06
Dayspostedfor 7
ExpiryDate 08/01/06
Any ideas would be really gratefully recieved
thanksGTN170777 wrote:
> Hi all, i would be really grateful if you could help with this one
> I have an MS SQL table with amongst others 3 fields -
> DatePosted
> Dayspostedfor
> ExpiryDate
> The value inserted into DatePosted id GetDate()
> The vaue inserted into Dayspostedfor is eith - 7/14/21 or 28
> I'd like ExpiryDate to calculate the other two to create an expiry date -
> for instance -
> DatePosted 01/01/06
> Dayspostedfor 7
> ExpiryDate 08/01/06
> Any ideas would be really gratefully recieved
> thanks
Lookup the DATEADD function in Books Online.
Is ExpiryDate always supposed to be equal to DatePosted +
Dayspostedfor? If so, then one of those columns is redundant. You
should decide which two of the columns to keep and drop the other from
the table. That's because redundant data leads to errors,
inefficiencies and over-complexity. If you just want to show both
values on the screen or in a report that doesn't mean you need to hold
them both in the table. You can do the calculation easily whenever you
need to.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First of all, adopt a single naming convention, namely ISO-11179. Next,
drop the redundant column (columns are not fields!! Important: Columns
can have constraints; fields do not) Start using CURRENT_TIMESTAMP
instead of the proprietary getdate(). Learn to use ISO-8601 date
formats.
CREATE TABLE Postings
(post_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
expiry_date DATETIME NOT NULL,
CHECK (post_date < expiry_date),
.);
date <<
Unh? If you already know the expiry date, why compute it? Also, we do
not do computations in an RDBMS. SQL is declarative, so you do such
things outside of the schema and store the data.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||ISO-11179 states you should use singular names and not plural.
You should call the table Posting and not Postings.
If you are going to follow a standard then don't invent things to support
your own style.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:eHU9ewgGGHA.2064@.TK2MSFTNGP09.phx.gbl...
> First of all, adopt a single naming convention, namely ISO-11179. Next,
> drop the redundant column (columns are not fields!! Important: Columns
> can have constraints; fields do not) Start using CURRENT_TIMESTAMP
> instead of the proprietary getdate(). Learn to use ISO-8601 date
> formats.
> CREATE TABLE Postings
> (post_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> expiry_date DATETIME NOT NULL,
> CHECK (post_date < expiry_date),
> ..);
>
> date <<
> Unh? If you already know the expiry date, why compute it? Also, we do
> not do computations in an RDBMS. SQL is declarative, so you do such
> things outside of the schema and store the data.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> ISO-11179 states you should use singular names and not plural. <<
For scalar data elements, not for data structures. I prefer a
collective name over a plural, and a plural name over a singular name
(unless there really is only one row in the table). Thus
Employee = bad. unless you are a very small company :)
Employees = better, since it shows that this is a set of more than one
element
Personnel = best, since it names the collection as such and not as the
grouping of many indivduals|||Hi again, sorry about this, but i am really struggleing with this. I would
like to show the expiry date but as i'm new to MS SQL - I'm lost. I guess th
e
other option would be to have two tables - adDatePosted value (default) =
(convert(char(8),getdate(),112)) and adExpiryDate value = either 7,14,21 or
28 and the run a query like the following through Dreamweaver MX
WHERE adDatePosted + adExpiryDate >= GETDATE()
Would this work? and if so would i need to use 7 or d,7 (7 days) in the
adExpiryDate value?
Thank you
"--CELKO--" wrote:

> For scalar data elements, not for data structures. I prefer a
> collective name over a plural, and a plural name over a singular name
> (unless there really is only one row in the table). Thus
> Employee = bad. unless you are a very small company :)
> Employees = better, since it shows that this is a set of more than one
> element
> Personnel = best, since it names the collection as such and not as the
> grouping of many indivduals
>|||On Tue, 17 Jan 2006 13:16:06 -0800, GTN170777 wrote:

>Hi again, sorry about this, but i am really struggleing with this. I would
>like to show the expiry date but as i'm new to MS SQL - I'm lost. I guess t
he
>other option would be to have two tables - adDatePosted value (default) =
>(convert(char(8),getdate(),112)) and adExpiryDate value = either 7,14,21 or
>28 and the run a query like the following through Dreamweaver MX
>WHERE adDatePosted + adExpiryDate >= GETDATE()
>Would this work? and if so would i need to use 7 or d,7 (7 days) in the
>adExpiryDate value?
>Thank you
Hi GTN170777,
There are three possibilities.
One: have the columns DatePosted (datetime or smalldatetime) and
DaysPostedFor (int, smallint or tinyint) in the table; use the
expression
DATEADD(day, DaysPostedFor, DatePosted)
whenever you need the expiry date.
Two: have the columns DatePosted and ExpiryDate (both datetime or
smalldatetime) in the table; use the expression
DATEDIFF(day, DatePosted, ExpiryDate)
whenever you need the number of days it's posted for.
Three: have the columns DaysPostedFor (int, smallint or tinyint) and
ExpiryDate (datetime or smalldatetime) in the table; use the expression
DATEADD(day, -DaysPostedFor, ExpiryDate)
whenever you need the date posted.
In all cases, if you prefer not to use the same expression over and over
again, you can use a VIEW or a computed column in the table.
Example of VIEW:
CREATE VIEW MyView
AS
SELECT Some other columns,
DatePosted,
DaysPostedFor,
DATEADD(day, DaysPostedFor, DatePosted) AS ExpiryDate
FROM YourTable
Example of computed column:
CREATE TABLE YourTable
(Other columns,
DatePosted smalldatetime NOT NULL,
DaysPostedFor int NOT NULL,
ExpiryDate AS DATEADD(day, DaysPostedFor, DatePosted),
More columns or constraints
)
Hugo Kornelis, SQL Server MVP

Date field transfer problem!

Hello guys!

I have a problem transferring certain table columns
to excel with DTS. I have a colums where I have date
and time record like 02.10.03 14.45.33.
When I transfer this to excel, it loses the time from that
colums and has only 02.10.03.
What could be the problem?

Please help me,
JessicaYou should store the time in a separate column.|||Hi Jessica,

how is the exported field defined? TIMESTAMP?
And, which Task do you use for the export?

At this point I can imagine that the destination field in Excel has got a format like "DD.MM.YYYY" and not "DD.MM.YYYY hh.mm.ss", so that Excel just hides this information.

Perhaps I am wrong... It just should be a guess ;-)

Greetings,

Carsten

date field select help

Help, I've got a field in this format that I can't break apart.

05/30/2005 00:00 00

I need to select records with this field greater than 02/01/2006 without disturbing the field, can i do this select in SQL ? Even with the hours/seconds in there?

Thanks,

Scooter

select Convert(datetime, STUFF ('05/30/2005 00:00 00',17,1,':'), 101)

from the inside out. . .
replace the 17th character in a given string with a ':'
use convert function to change to a datetime, interpreting the output of the stuff with format 101

|||But how do i select the records greater than 02/01/2006?|||select * from theTable
where Convert(datetime, STUFF (theDateField,17,1,':'), 101) >
convert(datetime, '02/01/2006', 101)|||

How about:

SELECT *

FROM Table1

where cast(substring('05/30/2005 00:00 00',0,11) as datetime) > '05/30/2005'

It's a bit simpler. You don't need to convert the constant '02/01/2006' to a datetime as it is recognised as a valid date time format. Simply replace '05/30/2005 00:00 00' with your field name.

HTH

For more SQL Tips, check out my blog:

|||

doesn't that depend on the localization settings?

in the states 12/1/2006 is December first 2006 . . . but in the uk its January first. . . isnt it?

|||Yes, much simpler, Thank you very much to both of you !!

Friday, February 24, 2012

Date Field Question

Hi,
In SQL server, I can use select dateadd(d, -1, getdate() ). How to do in
reporting ?
i.e. correct statement for dateadd(d, -1, Parameters!FromDate.Value)
Thank You!
TonyTony,
It should be the same.
Check out this link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdateadd.asp
Tony wrote:
> Hi,
> In SQL server, I can use select dateadd(d, -1, getdate() ). How to do in
> reporting ?
> i.e. correct statement for dateadd(d, -1, Parameters!FromDate.Value)
> Thank You!
> Tony|||SQL remains the same. If trying to calculate in reporting services, you need
quotes for the VB syntax.
= DateAdd("d", -1, Parameters!FromDate.Value)
"toolman" wrote:
> Tony,
> It should be the same.
> Check out this link:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdateadd.asp
>
> Tony wrote:
> > Hi,
> >
> > In SQL server, I can use select dateadd(d, -1, getdate() ). How to do in
> > reporting ?
> >
> > i.e. correct statement for dateadd(d, -1, Parameters!FromDate.Value)
> >
> > Thank You!
> > Tony
>|||Thanks All!
"William" wrote:
> SQL remains the same. If trying to calculate in reporting services, you need
> quotes for the VB syntax.
> = DateAdd("d", -1, Parameters!FromDate.Value)
>
> "toolman" wrote:
> > Tony,
> > It should be the same.
> > Check out this link:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdateadd.asp
> >
> >
> > Tony wrote:
> > > Hi,
> > >
> > > In SQL server, I can use select dateadd(d, -1, getdate() ). How to do in
> > > reporting ?
> > >
> > > i.e. correct statement for dateadd(d, -1, Parameters!FromDate.Value)
> > >
> > > Thank You!
> > > Tony
> >
> >

date field problem - "Value could not be converted because of a potential loss of data"

Hi,

I have a flat file that has a date column where the date fields look like 20070626, for example. No quotes.

The problem is that several of the date values are missing, and instead of the date value the field looks like this , ,

That is, there are several blank spaces where the date should be. The number of blank spaces between the commas doesn't appear to be a set number (and it could even be 8 blank spaces, I don't know, in which case I don't know if checking for the Len will produce the correct results, but that's another issue...)

So, similar to the numeric field blanks problem, I wrote a script to convert the field to null. This is the logic I used:

IfNot Len(Row.TradeDate) = 8 Then

Row.TradeDate_IsNull = True

EndIf

The next step in my data flow after the script is a derived column where I convert TradeDate from 20070625 to 06/25/2007. So the exact error message I am receiving is this:

[OLE DB Destination [547]] Error: There was an error with input column "TradeDate - derived" (645) on input "OLE DB Destination Input" (560). The column status returned was: "The value could not be converted because of a potential loss of data.".

Do I need to add a conditional split after the script and BEFORE the derived column to redirect bad rows so they don't go to the derived column?

What am I doing wrong here?

Thanks

Actually, I realize I don't want a conditional split because I don't want to throw the whole row away.

I just need to fix this "data conversion" problem.

|||Why not use a derived column to work with the date field and use the trim() function to get rid of the spaces, however many there may be? I see no need to invoke a script for this.

ISNULL(TRIM([TradeDate])) || [TradeDate] == "" || LEN([TradeDate]) < 8 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([TradeDate],5,2) + "/" + SUBSTRING([TradeDate],7,2) + "/" + SUBSTRING([TradeDate],1,4))|||

Hi,

I see your logic. I removed the script part and updated the derived column with your expression.

Now I get these errors:

[Derived Column [111]] Error: The conditional operation failed.

[Derived Column [111]] Error: The "component "Derived Column" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "TradeDate - derived" (541)" specifies failure on error. An error occurred on the specified object of the specified component.

In the flat file conn mgr, TradeDate is defined as a DT_STR, 8. I see that you are casting TradeDate to a DT_DBTIMESTAMP. I don't think that has anything to do with this error, but thought I would mention it.

I really don't know what the problem is here!

Thanks

|||I see I forgot a couple of TRIM() calls. Try this:

ISNULL(TRIM([TradeDate])) || TRIM([TradeDate]) == "" || LEN(TRIM([TradeDate])) < 8 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([TradeDate],5,2) + "/" + SUBSTRING([TradeDate],7,2) + "/" + SUBSTRING([TradeDate],1,4))

The output of the above is a datetime field. If you don't want that, and instead want it as a string, then just get rid of the (DT_DBTIMESTAMP) cast before the substrings.|||

Hi,

Why is it when I remove the (DT_DBTIMESTAMP) from NULL(DT_DBTIMESTAMP) the expression is bad?

I guess I don't understand this part: NULL(DT_DBTIMESTAMP)

Thanks

|||Nulls have to by typed accordingly. So that is outputting a NULL of type, DT_DBTIMESTAMP. If you want it to be a string, you'll need to do: NULL(DT_STR,10,1252)|||

That's what I figured, but of course I didn't know the syntax for casting to a string.

Casting to a DT_DBDATETIME is fine since that's what it ultimately is in the table.

Anyhow, it works now, thanks!

I see how I could use derived columns to solve the "blank numeric" problem in my previous post, but it's probably easier with the script, since I don't have to define a derived column output name, and it's less verbose.

|||

sadie519590 wrote:

I see how I could use derived columns to solve the "blank numeric" problem in my previous post, but it's probably easier with the script, since I don't have to define a derived column output name, and it's less verbose.

Up to you!

Saturday, February 25, 2012

Friday, February 24, 2012

Blog Archive

About Me