DateTime to Varchar

Im trying to convert a datetime value to a varchar
Ive used cast( datetimevalue as varchar(10)) but am not geting the desired
result. Im looking for a DD/MM/YYYY resultUse function CONVERT instead.
Example:
select convert(char(10), getdate(), 103)
AMB
"Peter Newman" wrote:

> Im trying to convert a datetime value to a varchar
> Ive used cast( datetimevalue as varchar(10)) but am not geting the desire
d
> result. Im looking for a DD/MM/YYYY result|||try this one
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
nivek
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3F16F075-1C14-4CCD-A91D-13231E67A071@.microsoft.com...
> Im trying to convert a datetime value to a varchar
> Ive used cast( datetimevalue as varchar(10)) but am not geting the
> desired
> result. Im looking for a DD/MM/YYYY result

datetime convertion in SqlServer 2000

SqlServer 2000 with SP3. RepTime is a datetime in TableA. I ran the two commands beblow:

SELECT distinct CAST([RepTime] AS INT) FROM TableA

39004
39002
39003

select max(RepTime),min(RepTime) from TableA

2006-10-16 10:36:03.940 2006-10-13 17:32:00.080

From 2006-10-13 to 2006-10-16, there are four days. But I got three distinct int from it. Anyone knows ?

Maybe you have two entries having the same date but different times?

DECLARE @.test AS TABLE (dat datetime);

INSERT into @.test values ('2006-12-19')
INSERT into @.test values ('2006-12-20')
INSERT into @.test values (GETDATE())

-- This retrieves 3 columns
SELECT DISTINCT dat FROM @.test

-- This retrieves 2 columns
SELECT DISTINCT CAST(dat AS INT) FROM @.test

That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.

|||

Very interesting while dig in to your issue..

SQL Server Converts the date using the following logic...

Declare @.Date as Datetime
Select @.Date = '2006-10-16 10:00:00'
select Cast(@.Date as Int) -- Result : 39004

Select @.Date = '2006-10-16 11:59:59'
select Cast(@.Date as Int) -- Result : 39004

Select @.Date = '2006-10-16 12:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)

Select @.Date = '2006-10-16 23:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)

Select @.Date = '2006-10-17 10:00:00'
select Cast(@.Date as Int) -- Result : 39005

Understood, the Integer cast will take one day from 12:00:00 PM to 11:59:59 AM (Strange Buddy = Bcs, after 12AM the value will be >= 39004.5 when the float number converted to integer it will round off the value...)

To overcome this issue use the following query..

SELECT distinct CAST(Convert(Datetime,Convert(Varchar,[RepTime],101)) AS INT) FROM TableA

Here we are omiting the Time field completly..

|||

Lucky P wrote:

That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.

Nope... Time is big concern here Lucky P

|||

You're completely right...

When converting to a Decimal Value, the Date '2006-10-16 12:00:00' is retrieved as 39004.5, which is rounded up to 39005 when converting it to INT.....

I made the mistake because im running the query before 12:00

|||

Yes absolutly it is round off issue... ..

So we can overcome this issue by the following query ..

SELECT distinct Cast(Round(CAST([RepTime] AS Float),0,2) as INT) FROM TableA

|||I got it. I made the same mistake too. Thanks to you all!|||

I use this one now:

SELECT distinct floor(cast([RepTime] as float)) FROM TableA

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
Clint
SQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>
|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> fine,
> date?
>

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
ClintSQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> fine,
> date?
>

DATETIME CAST statement

Hello,
I have a column in a view which is of the DATETIME datatype. This is fine,
but when I output this to MS Reporting Services it also shows the time
(which is always 12:00 as we are not using time as a field).
How do I use the cast statement or another statement to have only the date?
I have read BOL without success.
Thanks for any help provided.
ClintSQL doesn't provide a date only datatype. You have to handle this in the
client or truncate the time out in SQL doing something like this
select convert(char(10), getdate(), 110)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"AshVsAOD" <.> wrote in message
news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column in a view which is of the DATETIME datatype. This is
fine,
> but when I output this to MS Reporting Services it also shows the time
> (which is always 12:00 as we are not using time as a field).
> How do I use the cast statement or another statement to have only the
date?
> I have read BOL without success.
> Thanks for any help provided.
> Clint
>|||Thanks, that is what I was afraid of. I appreciate your response.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:%23sAdgEcIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> SQL doesn't provide a date only datatype. You have to handle this in the
> client or truncate the time out in SQL doing something like this
> select convert(char(10), getdate(), 110)
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "AshVsAOD" <.> wrote in message
> news:uubATmbIEHA.3664@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I have a column in a view which is of the DATETIME datatype. This is
> fine,
> > but when I output this to MS Reporting Services it also shows the time
> > (which is always 12:00 as we are not using time as a field).
> >
> > How do I use the cast statement or another statement to have only the
> date?
> > I have read BOL without success.
> >
> > Thanks for any help provided.
> >
> > Clint
> >
> >
>

DatePart function in ANSI SQL

Hi folks,

How can I re-write the following code in ANSI SQL code:

select cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+ cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar), event_instance_id, max(time_stamp)
from usmuser.usm_sli_event_data
where event_instance_id=10019
group by cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+
cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar),
event_instance_id
order by event_instance_id

Thanks for your help!
-Parulcould you please explain why?

also, for those of us not patient enough to unravel the intricacies of this delectable code fragment, would you kindly please explain what it's doing|||The code should be portable so it can used be used on other databases as well, not just SQL Server.
Basically, the time_stamp field has number of seconds since 1/1/1970 and the datepart function is calculating the month, day, and year. The goal is to get the last time_stamp per event_instance_id per day.|||unfortunately, your quest will be unsatisfied

date functions are among the more un-robust of the ANSI SQL capabilities

there is practically no hope that you will get exactly the same code to run "on other databases as well, not just SQL Server"

even if we did manage to figure out a way to do what you're doing with ANSI SQL functions (and good luck to you, as i'm going to pass), it probably wouldn't run on SQL Server to start with|||This is what data abstraction layers are for...|||Thanks r937!

Datename gives incorrect result

Hi!
I tried to run this query:
select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
The result is:
34 33
It's worng result, why?
Right answer is 33 in both datenamn item.
I have SQL Server 2000
Best regards
Bertil MorefltSQL Server doesn't calculate ws according to the ISO standard. I.e., don'
t use datepart or
datename for w number calculation. Search Books Online for ISOW and us
e that one instead. Or
use a calendar table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt|||If you are looking for ISOWEEK, you can find one at the CREATE Function
example in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt|||Here you will find a function to calculate the iso w.
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
AMB
"Bertil Morefalt" wrote:

> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moref?lt
>|||You can use a calendar table for this, or the ISOWEEK() function in Books
Online, or the one listed here:
http://www.aspfaq.com/2519
"Bertil Morefalt" <bertil@.community.nospam> wrote in message
news:%23rUlP0%23pFHA.1024@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I tried to run this query:
> select datename(wk,cast('Aug 15 2005 7:08AM' as datetime)),
> datename(wk,cast('Aug 14 2006 7:08AM' as datetime))
> The result is:
> 34 33
> It's worng result, why?
> Right answer is 33 in both datenamn item.
> I have SQL Server 2000
> Best regards
> Bertil Moreflt