Converting/Casting strings into Datetime datatype

Hello,

I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?

Thanks!

If you had only stored your date values in the ISO format of YYYYMMDD, they would easily cast or convert to datetime. -Or even left in one of the standard date delimiters, such as [ / - ].

However, you (or some unnamed 'other' person) made up a oddball format, and now you will have to 'handle' it to create a 'real' date value everytime you need to use it.

(This assumes your format is MMDDYYYY.)

SELECT cast( stuff( stuff( '03032007', 3, 0, '/' ), 6, 0, '/' ) AS datetime )

-
2007-03-03 00:00:00.000

|||

You know that MS stores sqlagent datetime in to two int columns with the following format, right? (Take a look at the schema for msdb: sysalerts,sysjobhistory, sysjobschedules, sysjobservers, and sysjobsteps.)

date: YYYYMMDD

time: HHMMSS

So, it's not that weird to see the public employs such schema.

|||

But I also notice that MS stores SQL Agent datetime in ISO format (YYYYMMDD).

That little 'standard' makes a lot of difference in cast/convert.

That is behind my even mentioning using a standard ISO format in my response...

converting varchar to date from asp.net to sort

for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:

CREATE PROCEDURE GetAllWeekEnding

AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO

if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.

Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand

MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()

Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While

mydr.Close()
MyConnection.Close()

the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion

any ideas?the .Add method expects a ListItem or a String. Try:

DropDownList1.Items.Add(mydr("we2").ToString())

Is there any reason you are not just binding to myDr?|||<<<<Is there any reason you are not just binding to myDr? >>>
yes, when i bind to the listbox, i am getting date and time.

i can get rid of the time by using:

While mydr.Read()
DropDownList1.Items.Add(Format(mydr("we2"), "M/dd/yyyy").ToString)
End While|||You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.|||<<<<<You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.
>>>>
the problem is that the date is already in varchar datatype in the database. on the aspx page, i want to load all the dates and i want it sorted by date. the only way for me to show the dates on the aspx page sorted by date is to convert the date from varchar to datetime, like this:

CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct convert(datetime, we) as we2 FROM tblArchive order by we2 desc
GO

once varchar is converted to datetime, it not only shows just the date but also time so i cannot bind it to a dropDownList.

if i just bind the orginal varchar on the DropDownList witout converting it to DateTime, it's not going to be sorted because you cannot sort a varchar like you sort a datetime|||CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct CONVERT(nvarchar(20),convert(datetime, we),101) as we2 FROM tblArchive order by convert(datetime, we) desc
GO

There is no rule that the representation you SELECT needs to be the same as you use to ORDER BY.

Converting unix time (PLEASE HELP!)

Oh please put me out of my misery and help me … I am pulling dates from my database which are in unix time. This means the time is displayed as seconds since 01/01/1970. I am trying to convert this to a date so I can create parameters for my report.

I have tried using convert and dateadd but nothing seems to work.

Any ideas?

Have you triedthis

converting unix dates

Hi,
Does anyone know of a quick an easy way of convertiing unix date serials eg
12815 to March 31 2005).
Any pointers appreciated
Thanks
SimonHi
I would have expected
select dateadd(ss, <UNIX_timestamp>, '19700101')
to have worked which implies 12815 either wrong or not a unix timestamp!!
John
"skilla31" wrote:

> Hi,
> Does anyone know of a quick an easy way of convertiing unix date serials e
g
> 12815 to March 31 2005).
> Any pointers appreciated
> Thanks
> Simon
>
>|||I suspect this isn't a standard Unix timestamp. Maybe 12815 represents
the number of days since some date. For example:
DECLARE @.t INTEGER
SET @.t = 12815
SELECT DATEADD(DAY,@.t,'19691231')
Result:
2005-01-31 00:00:00.000
(1 row(s) affected)
or:
SELECT DATEADD(DAY,@.t,'19700228')
Result:
2005-03-31 00:00:00.000
(1 row(s) affected)
DATEADD is probably the function you need in any case.
David Portas
SQL Server MVP
--|||Thanks
Jens, John & David, that solved my problem. Turns out I got the number wrong
should've been 12873
Thanks
Simon
"skilla31" <simon@.ris.org.uk> wrote in message
news:uN56AxOQFHA.3496@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Does anyone know of a quick an easy way of convertiing unix date serials
> eg 12815 to March 31 2005).
> Any pointers appreciated
> Thanks
> Simon
>|||"skilla31" <simon@.ris.org.uk> wrote in message
news:uN56AxOQFHA.3496@.TK2MSFTNGP12.phx.gbl...

> Hi,
> Does anyone know of a quick an easy way of convertiing unix date serials
> eg 12815 to March 31 2005).
SELECT DATEADD(d, 12815, '01 Jan 1970')

Converting strings to dates

Hi,

I have a varchar column with mixed date formats, some are dd-mm-yyyy and others are mmm dd yyyy hh:mmAP, these latter having been generated automatically by SQL when I changed the column datatype from datetime to varchar.

I would like to run an update script to pattern match the latter and change to the former.

I've been trying something along the lines of

update dwsubmit set authoreddate = (select day(authoreddate))+'-'+(select month(authoreddate))+'-'+(select year(authoreddate)) where id = 841

to try and get the format conversion correct, but this is obviously not correct. I have tried a few combinations, but have had no joy. Any ideas?

Thanks, Matt.I understand ur pain man...but u r doin it wrongly. Try this:

update dwsubmit
set authoreddate = (select datepart(authoreddate,dd))+'-'+(select datepart(authoreddate,mm))+'-'+(select datepart(authoreddate,yy))
where id = 841

op it works for u...av fun!|||sorry i mixed it up

update dwsubmit
set authoreddate = (select datepart(dd,authoreddate))+'-'+(select datepart(mm,authoreddate))+'-'+(select datepart(yy,authoreddate))
where id = 841

it should be ds way...sorry about dat...|||Thanks for that - I tried something similar, but SQL is actually adding the values up to come up with 2022 (04 + 12 + 2006). I have no idea what it is doing with the '-' characters. I then tried casting each part to char, but it ignores that too!|||i op u tried d 2nd version not d first...ol d same

try ds...

update dwsubmit
set authoreddate = select datepart(dd,authoreddate) + '-' + select datepart(mm,authoreddate) + '-' + select datepart(yy,authoreddate)
where id = 841|||Using what you gave me, I got to the following:

update dwsubmit
set authoreddate = cast((select datepart(dd,authoreddate)) as char(2))+'-'+cast((select datepart(mm,authoreddate)) as char(2))+'-'+cast((select datepart(yy,authoreddate)) as char(4))
where id = 841

Which gives me what I want unless there is only a single digit for the month or day, in which case I get something like 4 -12-2006 which should actually be 04-12-2006.

I'm sure the convert function is a better way of doing this, but I can't figure it out!

Thanks,
Matt.

Converting seconds to HHMMSS

My code calculates a duration of a start and end dates. It then converts the
duration into "
HHMMSS" format. My sample returns 0:0:3 which means 3 seconds.
Can someone help me modify my code so that the result would be 0:0:03 and
add the extra "padding 0" when the hours, minutes, or seconds are 1 digit?
CODE **************
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select rtrim(@.duration/3600) + ':' + rtrim(@.duration % 3600/60) + ':' +
rtrim(@.duration
% 60)does this work for you?
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
with 2 zeros
select convert(varchar,convert(datetime,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
one zero
select replace(convert(varchar,convert(datetime
,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
)),108),'00','0')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||that'll do it. thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
> does this work for you?
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 01:39:14'
> set @.dtEndDate = '20060314 01:39:17'
> with 2 zeros
> select convert(varchar,convert(datetime,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
> one zero
> select replace(convert(varchar,convert(datetime
,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
> )),108),'00','0')
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Scott,
Try using the function "right".
select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60),
2)
Be careful with the solution posted by SQL, It does not seem to work for
duration greater than 24 hours.
AMB
"Scott" wrote:

> that'll do it. thanks.
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
>
>|||> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
Neither this one when the duration is greater than or equal to 100 hours. we
need to get more than two digits.

> select right('00' + rtrim(@.duration/3600), 2) <--
AMB
"Alejandro Mesa" wrote:
> Scott,
> Try using the function "right".
> select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
> rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60
),
> 2)
> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
>
> AMB
> "Scott" wrote:
>

Converting report input dates to UTC

I have created a report in reporting services and it takes a number of
DateTime values as input parameters. These values will be passed on to
queries in my database; the problem is that my database stores DateTime
values as UTC.
Is there a way I can convert them to UTC before the values are sent to the
query?Hello,
You could try this:
DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @.LocalDate)
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Converting report input dates to UTC
>thread-index: AcYtdvuTa+Zu+2dZQC6M3qdl1xmC3Q==>X-WBNR-Posting-Host: 195.139.24.170
>From: "=?Utf-8?B?Q2hyaXN0b3BoZXIgS2ltYmVsbA==?="
<c_kimbell@.newsgroup.nospam>
>Subject: Converting report input dates to UTC
>Date: Thu, 9 Feb 2006 04:47:27 -0800
>Lines: 7
>Message-ID: <920BBD92-A5C6-4A43-B965-8D1AE0151687@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68388
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I have created a report in reporting services and it takes a number of
>DateTime values as input parameters. These values will be passed on to
>queries in my database; the problem is that my database stores DateTime
>values as UTC.
>Is there a way I can convert them to UTC before the values are sent to the
>query?
>

Converting Output Dates

My input data is in the date format: DD/MM/YY. I have 2 parameters Start date and end date. The query runs fine and returns the data between start and end date.
The problem is that it converts the dates into MM/DD/YY. What it does is it converts 01/12/05 (1st December 2005) to display 12/01/2005 (12th January 2005).
Also when i export the data its format shows 12 as the date instead of month.

The server is in AUstralia and running on British format (DD/MM/YY).

Any help will be appreciated.

Thanks,
Nit

What's the client culture when you export the report? And what is the server OS culture? Does the RDL have a specified language setting?

We use the client culture when formatting the parameter value, and server OS culture when formatting the textbox value in the report (if there's no language specified in the RDL).

- Fang