DateTime Parameter need yyyy-mm-dd format

I have a parm defined as DateTime in RS2005 (so you see the calendar control
for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
display yyyy-mm-dd as it was when we had date defined as string rather than
DateTime. I went into regional settings on my pc and changed the date
setting in my regional options to display date as yyyy-MM-dd and it still
didnt seem to work. Can someone direct me? ThanksHi.
You have a language setting in the reports properties.
The default setting for language is "English (United States)". You can
change that either to what the user have choosen in IE by chosing "Default"
or what you want it to be. If you need yyyy-MM-dd you just change to your
country's setting.
You can override this setting for certain textboxes if you like. Textboxes
have their own language setting.
Kind regards
Maran
*********************
"MJT" wrote:
> I have a parm defined as DateTime in RS2005 (so you see the calendar control
> for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> display yyyy-mm-dd as it was when we had date defined as string rather than
> DateTime. I went into regional settings on my pc and changed the date
> setting in my regional options to display date as yyyy-MM-dd and it still
> didnt seem to work. Can someone direct me? Thanks|||Thank you Maran,
I had changed my regional settings in control panel prior to posting my
message, but I didnt realize that they wouldnt take effect until I closed out
of VS and re-entered. Which brings another question to mind. If we want the
date displayed as yyyy-MM-dd format can that be accomplished solely by
setting to "English(United States)"? Because when I noticed the problem
initially, I had my windows settings for date format as MM-dd-yyyy and my
language was English(United States) and that seemed to be why I saw the date
format as MM-dd-yyyy when I changed my parameter to DateTime format. I am
wondering if the users have their date format in windows set other than
MM-dd-yyyy if just making the language English(United States) will matter at
all now. Since that was the way my settings were originally. Is there a way
to enforce a date time format? I realize that each textbox has a format
parameter, but this textbox that referenced the date parameter had other code
in it which was being translated into multiple languages in a code block - so
I cant use the format property on the textbox itself - I would have to put it
in the code block somehow. Thanks again for your reply.
"Maran" wrote:
> Hi.
> You have a language setting in the reports properties.
> The default setting for language is "English (United States)". You can
> change that either to what the user have choosen in IE by chosing "Default"
> or what you want it to be. If you need yyyy-MM-dd you just change to your
> country's setting.
> You can override this setting for certain textboxes if you like. Textboxes
> have their own language setting.
> Kind regards
> Maran
> *********************
> "MJT" wrote:
> > I have a parm defined as DateTime in RS2005 (so you see the calendar control
> > for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> > display yyyy-mm-dd as it was when we had date defined as string rather than
> > DateTime. I went into regional settings on my pc and changed the date
> > setting in my regional options to display date as yyyy-MM-dd and it still
> > didnt seem to work. Can someone direct me? Thanks|||I handled this by adding the format to my custom code. Thanks.
"MJT" wrote:
> Thank you Maran,
> I had changed my regional settings in control panel prior to posting my
> message, but I didnt realize that they wouldnt take effect until I closed out
> of VS and re-entered. Which brings another question to mind. If we want the
> date displayed as yyyy-MM-dd format can that be accomplished solely by
> setting to "English(United States)"? Because when I noticed the problem
> initially, I had my windows settings for date format as MM-dd-yyyy and my
> language was English(United States) and that seemed to be why I saw the date
> format as MM-dd-yyyy when I changed my parameter to DateTime format. I am
> wondering if the users have their date format in windows set other than
> MM-dd-yyyy if just making the language English(United States) will matter at
> all now. Since that was the way my settings were originally. Is there a way
> to enforce a date time format? I realize that each textbox has a format
> parameter, but this textbox that referenced the date parameter had other code
> in it which was being translated into multiple languages in a code block - so
> I cant use the format property on the textbox itself - I would have to put it
> in the code block somehow. Thanks again for your reply.
> "Maran" wrote:
> > Hi.
> >
> > You have a language setting in the reports properties.
> >
> > The default setting for language is "English (United States)". You can
> > change that either to what the user have choosen in IE by chosing "Default"
> > or what you want it to be. If you need yyyy-MM-dd you just change to your
> > country's setting.
> >
> > You can override this setting for certain textboxes if you like. Textboxes
> > have their own language setting.
> >
> > Kind regards
> > Maran
> >
> > *********************
> >
> > "MJT" wrote:
> >
> > > I have a parm defined as DateTime in RS2005 (so you see the calendar control
> > > for the parm) .. when selected ... the format is mm/dd/yyyy and I need it to
> > > display yyyy-mm-dd as it was when we had date defined as string rather than
> > > DateTime. I went into regional settings on my pc and changed the date
> > > setting in my regional options to display date as yyyy-MM-dd and it still
> > > didnt seem to work. Can someone direct me? Thanks

Datetime format

Hi all,
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' format
How do i customize this format?
For example i want the value like 'ddmonyyyy hh:mm'
Help required.
Thanx in anticipation.
'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA

>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>

Datetime format

Hi all,
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' format
How do i customize this format?
For example i want the value like 'ddmonyyyy hh:mm'
Help required.
Thanx in anticipation.'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA

>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>

Datetime format

Hi all
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' forma
How do i customize this format
For example i want the value like 'ddmonyyyy hh:mm
Help required
Thanx in anticipation.'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
--
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA
>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>

Datetime and null value

In my stored procedure i'm extracting datevalues from a table and printing t
hem
in yyyy-mm-dd hh:mi:ss format.
for example if the table value is 'Jul 16 2004 12:00AM' then my statement
(which is dynamically generated)
select Convert(CHAR(20),cast('Jul 16 2004 12:00AM ' as datetime),20)
will print " 2004-07-16 00:00:00 "
but if the table's datevalue is null then the statement
select Convert(CHAR(20),cast(' ' as datetime),20)
is printing "1900-01-01 00:00:00 "
I want the second one to be blank value(' ') what should I do?
Thanks
Chandra
Declare @.t datetime
set @.t='2005-07-16 12:00:00'
select case when @.t is null then convert(varchar,'',101) else
Convert(CHAR(20),cast(@.t as datetime),20) end
set @.t=null
select case when @.t is null then convert(varchar,'',101) else
Convert(CHAR(20),cast(@.t as datetime),20) end
Madhivanan|||Actually I mentioned that it is dynamically generated statement
like the following
select 'insert into employee (hire_date) values ( Convert(CHAR(20),cast('''+
isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
will give you an insert statement.
this insert statement when run, will insert the data into table.
at this point I'm having the problem as the insert statement is inserting
default date(1900...) for empty strings(actually null values)
thanks
chandra
"Madhivanan" wrote:

>
> Declare @.t datetime
> set @.t='2005-07-16 12:00:00'
> select case when @.t is null then convert(varchar,'',101) else
> Convert(CHAR(20),cast(@.t as datetime),20) end
> set @.t=null
> select case when @.t is null then convert(varchar,'',101) else
> Convert(CHAR(20),cast(@.t as datetime),20) end
>
> Madhivanan
>|||Chandra
declare @.dt datetime
set @.dt =''
select @.dt
--1900-01-01 00:00:00.000
select case when @.dt ='' then null else @.dt end as d
--NULL
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:FEA86C39-4E3D-4D66-9588-1421CB53A918@.microsoft.com...
> Actually I mentioned that it is dynamically generated statement
> like the following
> select 'insert into employee (hire_date) values (
> Convert(CHAR(20),cast('''+
> isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
> will give you an insert statement.
> this insert statement when run, will insert the data into table.
> at this point I'm having the problem as the insert statement is inserting
> default date(1900...) for empty strings(actually null values)
> thanks
> chandra
>
> "Madhivanan" wrote:
>|||> Actually I mentioned that it is dynamically generated statement
> like the following
> select 'insert into employee (hire_date) values (
> Convert(CHAR(20),cast('''+
> isnull(cast(Hire_date as char),'')+ ''' as datetime),20))' from employee
> will give you an insert statement.
> this insert statement when run, will insert the data into table.
> at this point I'm having the problem as the insert statement is inserting
> default date(1900...) for empty strings(actually null values)
Can you tell us what you WANT to insert when the Hire_date is NULL?
I'll make a guess:
SELECT 'INSERT employee (hire_date)
SELECT '+COALESCE(CONVERT(VARCHAR(8), Hire_date, 112), 'NULL')
FROM employee|||> SELECT 'INSERT employee (hire_date)
> SELECT '+COALESCE(CONVERT(VARCHAR(8), Hire_date, 112), 'NULL')
> FROM employee
Whoops, should be:
SELECT 'INSERT employee (hire_date)
SELECT '+COALESCE(''''+CONVERT(VARCHAR(8), Hire_date, 112)+'''', 'NULL')
FROM employee