DateTime Format Conversion Issue

Hello everyone,

I came accross this problem where I can't change my stored date to:

m/d/yyyy hh:mi:ss AM

Seems to me like there is no code related to that particular conversion.

I'm just assuming that because I've tried all the codes supplied with the convert, and the closest that I got was

selectCONVERT(nvarchar(max),getdate(),22)

02/27/07 12:24:40 PM

m/d/yy hh:mi:ss AM

So can any one give me a quick solution for this? I'd appreciate it

select

RIGHT(CAST(100+DATEPART(MM,createdDate)ASCHAR(3)),2)+'/'

+RIGHT(CAST(100+DATEPART(DD,createdDate)ASCHAR(3)),2)+'/'

+CAST(DATEPART(YYYY,createdDate)ASCHAR(4))+' '

+CASEWHENDATEPART(HH,createdDate)< 13

THENRIGHT(CAST(100+DATEPART(HH,createdDate)ASCHAR(3)),2)

ELSECAST(DATEPART(HH,createdDate)-12ASCHAR(2))

END+':'

+DATENAME(ss, createdDate)+':'

+RIGHT(CAST(100+DATEPART(MI,createdDate)ASCHAR(3)),2)

+CASEWHENDATEPART(HH,createdDate)< 13

THEN' AM'

ELSE' PM'

END

from files

|||

How about

selectCONVERT(nvarchar(max),getdate(),101)+substring(CONVERT(nvarchar(max),getdate(),22), 9, 50)

?

Dan

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