convert dates

Thursday, March 29, 2012

Converting Datetime to Date

I need to convert a datetime, coming from a database table, to a date. How
do I do this within my report?
--
DonIs this just for display or do you really want to convert to date for further
manipulation as a date?
If it is only for display, use =format(Fields!MyDateField.Value,
"MMM-dd-yyyy") or something like it.
If it is for further manipulation as a date, use
=CDate(Fields!MyDateField.Value)
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> I need to convert a datetime, coming from a database table, to a date. How
> do I do this within my report?
> --
> Don|||This is just for display. I have a related problem. In this report I have
multiple datasets. With the multiple datasets If I drag a dataset field onto
the report designer and look at the default expression for the field it looks
like this:
=First(Fields!DateSigned.Value, "AppDetailDS")
The "AppDetailDS" being the dataset name.
I tryed modifying 2 different ways as follows:
=Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
=Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
I cant seem to get the syntax correct when modifying the expression. I get
errors when I try to preview.
Also, I have tried using immediate if's(iif) and cant get that to work. I
have no problem in a report with only one dataset.
Any thoughts.
"Charles Kangai" wrote:
> Is this just for display or do you really want to convert to date for further
> manipulation as a date?
> If it is only for display, use =format(Fields!MyDateField.Value,
> "MMM-dd-yyyy") or something like it.
> If it is for further manipulation as a date, use
> =CDate(Fields!MyDateField.Value)
> HTH
> Charles Kangai, MCT, MCDBA
>
> "Don" wrote:
> > I need to convert a datetime, coming from a database table, to a date. How
> > do I do this within my report?
> >
> > --
> > Don|||Remove the "AppDetailDS" from your Format formula below, then capitalize the
"mmm" to "MMM". You only need two parameters for the Format function.
You should just have
=Format(Fields!DateSigned.Value,"MM-dd-yyyy")
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> This is just for display. I have a related problem. In this report I have
> multiple datasets. With the multiple datasets If I drag a dataset field onto
> the report designer and look at the default expression for the field it looks
> like this:
> =First(Fields!DateSigned.Value, "AppDetailDS")
> The "AppDetailDS" being the dataset name.
> I tryed modifying 2 different ways as follows:
> =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> I cant seem to get the syntax correct when modifying the expression. I get
> errors when I try to preview.
> Also, I have tried using immediate if's(iif) and cant get that to work. I
> have no problem in a report with only one dataset.
> Any thoughts.
> "Charles Kangai" wrote:
> > Is this just for display or do you really want to convert to date for further
> > manipulation as a date?
> >
> > If it is only for display, use =format(Fields!MyDateField.Value,
> > "MMM-dd-yyyy") or something like it.
> >
> > If it is for further manipulation as a date, use
> > =CDate(Fields!MyDateField.Value)
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> >
> >
> > "Don" wrote:
> >
> > > I need to convert a datetime, coming from a database table, to a date. How
> > > do I do this within my report?
> > >
> > > --
> > > Don|||If I remove the "AppDetailDS", When I preview, I get an error: "the value
expression for the textbox 'DateSigned' uses an aggregate expression without
a scope. A scope is required for all aggregates use outside of a data region
unless the report contains exactly one data set.
"Charles Kangai" wrote:
> Remove the "AppDetailDS" from your Format formula below, then capitalize the
> "mmm" to "MMM". You only need two parameters for the Format function.
> You should just have
> =Format(Fields!DateSigned.Value,"MM-dd-yyyy")
> HTH
> Charles Kangai, MCT, MCDBA
> "Don" wrote:
> > This is just for display. I have a related problem. In this report I have
> > multiple datasets. With the multiple datasets If I drag a dataset field onto
> > the report designer and look at the default expression for the field it looks
> > like this:
> > =First(Fields!DateSigned.Value, "AppDetailDS")
> > The "AppDetailDS" being the dataset name.
> >
> > I tryed modifying 2 different ways as follows:
> > =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> > =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> >
> > I cant seem to get the syntax correct when modifying the expression. I get
> > errors when I try to preview.
> >
> > Also, I have tried using immediate if's(iif) and cant get that to work. I
> > have no problem in a report with only one dataset.
> >
> > Any thoughts.
> >
> > "Charles Kangai" wrote:
> >
> > > Is this just for display or do you really want to convert to date for further
> > > manipulation as a date?
> > >
> > > If it is only for display, use =format(Fields!MyDateField.Value,
> > > "MMM-dd-yyyy") or something like it.
> > >
> > > If it is for further manipulation as a date, use
> > > =CDate(Fields!MyDateField.Value)
> > >
> > > HTH
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > >
> > >
> > > "Don" wrote:
> > >
> > > > I need to convert a datetime, coming from a database table, to a date. How
> > > > do I do this within my report?
> > > >
> > > > --
> > > > Don|||Try to not put textboxes outside of data regions. My suggestion is that you
use containers such as list or table data region to put your textboxes in.
The Format function does not have a scope parameter, so it should work. The
First function you are using is an aggregate function, so it may need a scope
parameter.
But the first thing you need to do is to place a list or table data region
on your screen. Bind it to a dataset using the Properties dialog, then add
textboxes inside of it.
HTH
Charles Kangai, MCT, MCDBA
"Don" wrote:
> If I remove the "AppDetailDS", When I preview, I get an error: "the value
> expression for the textbox 'DateSigned' uses an aggregate expression without
> a scope. A scope is required for all aggregates use outside of a data region
> unless the report contains exactly one data set.
> "Charles Kangai" wrote:
> > Remove the "AppDetailDS" from your Format formula below, then capitalize the
> > "mmm" to "MMM". You only need two parameters for the Format function.
> > You should just have
> > =Format(Fields!DateSigned.Value,"MM-dd-yyyy")
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Don" wrote:
> >
> > > This is just for display. I have a related problem. In this report I have
> > > multiple datasets. With the multiple datasets If I drag a dataset field onto
> > > the report designer and look at the default expression for the field it looks
> > > like this:
> > > =First(Fields!DateSigned.Value, "AppDetailDS")
> > > The "AppDetailDS" being the dataset name.
> > >
> > > I tryed modifying 2 different ways as follows:
> > > =Format(Fields!DateSigned.Value,"AppDetailDS","mm-dd-yyyy")
> > > =Format(Fields!DateSigned.Value,"mm-dd-yyyy","AppDetailDS")
> > >
> > > I cant seem to get the syntax correct when modifying the expression. I get
> > > errors when I try to preview.
> > >
> > > Also, I have tried using immediate if's(iif) and cant get that to work. I
> > > have no problem in a report with only one dataset.
> > >
> > > Any thoughts.
> > >
> > > "Charles Kangai" wrote:
> > >
> > > > Is this just for display or do you really want to convert to date for further
> > > > manipulation as a date?
> > > >
> > > > If it is only for display, use =format(Fields!MyDateField.Value,
> > > > "MMM-dd-yyyy") or something like it.
> > > >
> > > > If it is for further manipulation as a date, use
> > > > =CDate(Fields!MyDateField.Value)
> > > >
> > > > HTH
> > > >
> > > > Charles Kangai, MCT, MCDBA
> > > >
> > > >
> > > >
> > > > "Don" wrote:
> > > >
> > > > > I need to convert a datetime, coming from a database table, to a date. How
> > > > > do I do this within my report?
> > > > >
> > > > > --
> > > > > Don

converting datetime int

I have tables with columns that stores datetime data in int format on
SQL server 2000. For example, the datetime for '4/5/2004
00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
1081180799. I need to generate reports that display datetime columns
in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
my query statment.

select iorg_name as org, ref_num as [ticketnum], c_first_name as
[firstname], c_last_name as [lastname], sym as type, [description] as
summary, status, dateadd(s,open_date,'12/31/1969 08:00:00pm') as
opened, dateadd(s,last_mod_dt,'12/31/1969 08:00:00pm') as irt,
dateadd(s,close_date,'12/31/1969 08:00:00pm') as closed from
AHD.dbo.HDreports reportview WHERE reportview.open_date >= 1080882000
AND reportview.open_date <= 1081227599.

The result shows correctly with those records that are in daylight
saving time. Those records in standard time show 1 hour behind.

Does anyone know how to make this query correctly display the data in
properly?js (androidsun@.yahoo.com) writes:
> I have tables with columns that stores datetime data in int format on
> SQL server 2000. For example, the datetime for '4/5/2004
> 00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
> 1081180799. I need to generate reports that display datetime columns
> in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
> my query statment.
>...
> The result shows correctly with those records that are in daylight
> saving time. Those records in standard time show 1 hour behind.
> Does anyone know how to make this query correctly display the data in
> properly?

That was a very odd way of storing dates, and probably not the best one.
Apparently this is some variation of Unix, where time is counted as number
of seconds since 1970-01-01 00:00:00, except that here the staring point
is 1969-12-30 20:00:00.

SQL Server is not timezone aware, so you should not expect to be able
to get fully accurate results. You are probably best of getting the
integer value to the client, and try the Windows functions for date
and time. They are likely to work out better.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting datetime in a string

Hi there,

Can anyone help?

I am trying to convert datetime into a string in the following format

2003-10-09 13:23:15.967

Current syntax:

SELECT '"' + CAST(t.Stamp AS varchar(30)) + '"' AS 'Start'

Any one any ideas?

THANKYOU!!!!

Melanie :)Try this:

select convert(varchar,getdate(),121)sql sql

Converting Datetime from the Varchar value

I am not sure if this is the correct forum to post to for this but,

I have a stored procedure in the code like so:


dim calensql as string = "sp_scheduleworkfromcal '" & sun & "', '" & mon & "', '" & tue & "', '"
& wed & "', '" & thu & "', '" & fri & "', '" & sat & "', '" & Label1.Text & "', '" & Label2.Text & "', '"
& Label3.Text & "', '" & Label4.Text & "', '" & Label5.Text & "', '" & Label6.Text & "', '" & Label7.Text & "', " & "129"

and then in my stored procedure I have


CREATE PROCEDURE sp_scheduleworkfromcal
(@.sun VarChar(50), @.mon VarChar(50), @.tue VarChar(50), @.wed VarChar(50), @.thu VarChar(50),
@.fri VarChar(50), @.sat VarChar(50), @.dsun VarChar(50), @.dmon VarChar(50), @.dtue VarChar(50),
@.dwed VarChar(50), @.dthu VarChar(50), @.dfri VarChar(50), @.dsat VarChar(50), @.userid int)
AS
Declare @.store datetime
If @.sun != '' begin
 Update servicerequests set date_scheduled=(Convert(datetime, @.dsun)) where trackingnumber=@.sun
 Select @.store = rtrim(retailer) + ' ' + rtrim(storeNumber) from servicerequests where trackingnumber = @.sun
 UPDATE CalendarSchedule SET cal_notes=@.store WHERE cal_date=@.dsun AND userid=@.userid
 IF @.@.ROWCOUNT = 0
 INSERT INTO CalendarSchedule (userid, cal_date, cal_notes) VALUES (@.userid, @.dsun, @.store)
End

and I am getting the error something like
Syntax error converting datetime from character string.

If I change the parameters in the stored procedure to datetime or varchar and get rid of the single quotes, I get incorrect sytax near "/".

I am tracking the sql statement to see where I can fix the problem, but cannot come up with a solution.

can anyone help me out with this one??
Thanks
EricI think the date format you are trying to create is wrong ... At the location ... Try investigating the line :
@.store = rtrim(retailer) + ' ' + rtrim(storeNumber)|||I don't know whether I should use datetime in the values or varchar.
In the two tables
The one the field is a datetime field and in the other table it is a Char(15)

why won't it recognize the sql statement
SQL Statement sp_scheduleworkfromcal '897', '', '', '', '903', '', '', '10/19/2003', '10/20/2003', '10/21/2003', '10/22/2003', '10/23/2003', '10/24/2003', '10/25/2003', 129

thats the trace.

E|||I have tried a variation of your code and am not receiving any errors.

Where exactly are you getting the error? Which line number, and what is the error message exactly?

What are the data types and lengths of the following columns?
-- servicerequests.date_scheduled
-- servicerequests.trackingnumber
-- CalendarSchedule.cal_date

Terri|||servicerequests.date_scheduled datetime(8)
servicerequests.trackingnumber bigint(8)
CalendarSchedule.cal_date datetime(8)

I am getting the error on the
cmd.ExecuteNonQuery() line
and the error is as follows:
System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I changed the values of the textboxes from the page in the sproc from VarChar(50) to datetime as well as the data type in the CalendarSchedule table from Char(15) to datetime and still am getting the same error.

You say you tried a variation of the code? what about it did you change?
are my datatype's wrong?

Thanks
E|||I mean labels from Varchar(50) to datetime - not textboxes

Converting datetime from character string

Hi there,

I have the following code:


Dim CityTown As String = Ctype(Request.Querystring("CityTown"), String)
 Dim Suburb As String = Ctype(Request.Querystring("Suburb"), String)
 Dim SuburbValue As String = Ctype(Request.Querystring("Suburb"), String)
 Dim Rooms As String = Ctype(Request.Querystring("Rooms"), String)
 Dim Rent As String = Ctype(Request.Querystring("Rent"), String)Dim DateToday = DateTime.Now
Dim mySQL AS String
If suburbValue = "- All -" Then
mySQL = "SELECT propListID, propListExpires, propBuildType, propRoomNumber, propRentPerWeek, propDescription, propCityTown, propSuburb FROM tblPropertyList WHERE [propCityTown]='" & CityTown & "' AND [propRentPerWeek]<= '" & Rent & "' AND [propRoomNumber]='" & Rooms & "'AND [propListExpires] >='" & DateToday & "' "

However I am having trouble getting the Date to work as part of my SQL String. I keep getting the following error:

Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.

Source Error:

Line 30: MyDA = New SqlDataAdapter (mySQL, myConn)
Line 31: myDS = New DataSet()
Line 32: myDA.Fill(MyDS)
Line 33:
Line 34: dlPropertyListing.Datasource = MyDS.Tables(0)

Source File: K:\details\detailspropertylisting.aspx Line: 32

Any ideas how to resolve this?? I do not want to use Parameters in my statement.

Thanks in advance,

TCMYou can modify the query as :

mySQL = "SELECT propListID, propListExpires, propBuildType, propRoomNumber, propRentPerWeek, propDescription, propCityTown, propSuburb FROM tblPropertyList WHERE [propCityTown]='" & CityTown & "' AND [propRentPerWeek]<= '" & Rent & "' AND [propRoomNumber]='" & Rooms & "'AND [propListExpires] >=getDate()"

Hope this helps.|||and use parameterized queries too.

hth|||Thanks so much for the response.

Worked perfectly.

TCM

converting datetime formats and layouts?

Opening DimTime table of AdventureWorksDW sample database in MS SQL Server Management Studio shows me values in FullDateAlternatKey like

01.07.2001 0:00:00

select fulldatealternatekey from dimtime
gives me the results like
2001-07-01 00:00:00.000

(1) Why is it?
(2)How can I "SELECT fulldatealternatekey" in different format like 9/3/2001 0:00
(that is given in SampleCurrencyData.txt ?
Well, I am trying to follow Integration Service Tutorial, Lesson1 (SQL Server 2005 Books Online) and have yet another format of datetime like 9/3/2001 0:00 in SampleCurrencyData.txt and receive the type mismatch in part "To add and configure the DateKey Lookup transformation" (8.In the Available Input Columns panel, drag CurrencyDate to the Available Lookup Columns panel and drop it on FullDateAlternateKey.)

Flat File Connection Manager Editor --> Advanced --> DataType gives me following formats:
- file_timestamp [DT_FILETIME]
- database date [DT_DBDATE]
- database time [DT_DBTIME]
- database timestamp [DT_DBTIMESTAMP]
- date [DT_DATE]
- file timestamp [DT_FILETIME]

(3)
BOL2005 give me general description of these date-times but how do I govern separation delimitoers in them (., /, -)?

That is the recommended way to store dates in SQL Server. The best way to represent dates in your application is to extract them as-is from the database, and format them using the application language's built-in string formatting functions. Here is a list of them for C# -http://blog.stevex.net/index.php/string-formatting-in-csharp/|||

Really, my problem has nothing to do with C# or storing dates by ME (?!) since I am trying to reproduce SSIS tutorial (as I mentioned it more specifically above).

I found some related posts to my problem:
http://forums.asp.net/thread/1688990.aspx
converting datetime formats and layouts?

ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

Though neither of tholutions helped me!

|||I see that I cited this same post.
Instead, one more post that describes the same problem bnut had not helped me is

collation or local sensitive settings or other configuration properties
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=180740&SiteID=1

converting datetime for comparison

I am passing a datetime to a stored proc and I need to convert this
datetime to the format dd/mm/yyyy and then make a comparison between it
and a field in my select statement. Can somebody tell me what I am
doing wrong in my code?
declare @.Company varchar(50)
declare @.SerialNumber varchar(50)
declare @.ProductGroup varchar(10)
declare @.InstallationDate datetime
set @.Company = ''
set @.SerialNumber = ''
set @.ProductGroup = '9'
set @.InstallationDate = '21/03/2003 00:00:00'
select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
p.InstallationDate,
t.ProductTypeID, t.[Description], convert(datetime,
left(p.InstallationDate, 11), 103),
convert(datetime, left(@.InstallationDate, 11), 103)
from Products p
inner join companysite s
on p.OwnedByCompanyID = s.CompanySiteKey
inner join Competitors c
on c.CompetitorID = p.ProducedByID
inner join CompetitorMachines m
on p.MachineID = m.CompetitorMachineID
inner join ProductTypes t
on t.ProductTypeID = m.ProductTypeID
where s.[name] like '%' + @.Company + '%'
and p.SerialNumber like '%' + @.SerialNumber + '%'
and t.ProductTypeID like '%' + @.ProductGroup + '%'
and convert(char(11), left(p.InstallationDate, 11), 103) =
convert(char(11), left(@.InstallationDate, 11), 103)
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" wrote ...

> Can somebody tell me what I am doing wrong in my code?
A guess only...but..

> declare @.InstallationDate datetime
^^ a datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'

> convert(char(11), left(@.InstallationDate, 11), 103)
char(11)
Perhaps the different data types is the problem?
Something else you could try which might help you achieve the same result
would be using the DATEDIFF function, if you are trying to find matches for
the same day, just use DATEDIFF, specify days, and look for where it equals
0 (ie, no days difference)..
Hope this helps..
Rob|||I have some best practices for datetime here: http://www.karaszi.com/SQLServer/in...
o_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike P" <mike.parr@.gmail.com> wrote in message news:e3zsKC2nGHA.3348@.TK2MSFTNGP03.phx.gbl.
.
>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
> declare @.Company varchar(50)
> declare @.SerialNumber varchar(50)
> declare @.ProductGroup varchar(10)
> declare @.InstallationDate datetime
> set @.Company = ''
> set @.SerialNumber = ''
> set @.ProductGroup = '9'
> set @.InstallationDate = '21/03/2003 00:00:00'
> select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
> p.InstallationDate,
> t.ProductTypeID, t.[Description], convert(datetime,
> left(p.InstallationDate, 11), 103),
> convert(datetime, left(@.InstallationDate, 11), 103)
> from Products p
> inner join companysite s
> on p.OwnedByCompanyID = s.CompanySiteKey
> inner join Competitors c
> on c.CompetitorID = p.ProducedByID
> inner join CompetitorMachines m
> on p.MachineID = m.CompetitorMachineID
> inner join ProductTypes t
> on t.ProductTypeID = m.ProductTypeID
> where s.[name] like '%' + @.Company + '%'
> and p.SerialNumber like '%' + @.SerialNumber + '%'
> and t.ProductTypeID like '%' + @.ProductGroup + '%'
> and convert(char(11), left(p.InstallationDate, 11), 103) =
> convert(char(11), left(@.InstallationDate, 11), 103)
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>I am passing a datetime to a stored proc and I need to convert this
> datetime to the format dd/mm/yyyy and then make a comparison between it
> and a field in my select statement. Can somebody tell me what I am
> doing wrong in my code?
Well, what is the problem? Do you get an error message, or is it not
returning any rows? Could you give us DDL, sample data, and desired results
as per http://www.aspfaq.com/5006 ?