Date question

Hello everybody i do have varchar column with DD-MM-YYYY. How can i convert
it to datetime data type ?
pls let me knowDo you need to jsut convert it within a query, you can use Cast of Convert
for that.
If you need to convert the column's data type, You have two options.
1) in T-SQL add the new column, do an update to move the data over, drop the
old column, then rename the new column
2) in Enterprise Manager change the data type and it will create a script
for you.
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:BAD8F615-56DF-47EB-93A7-26A8AC5C0920@.microsoft.com...
> Hello everybody i do have varchar column with DD-MM-YYYY. How can i
> convert
> it to datetime data type ?
> pls let me know

Date question

I currently get a date in the default format in my stored procedure. Up until now converting the date to type 101 was acceptable. Now they want the date to read something like Wednesday, November 8, 2006 or November 8, 2006. How do I do this convert in a single select statement?

I found the types that add the time but I have to make sure that the time isn't included for merge reasons.

Help

Jeff

Mookey:

Are you looking for something like this:

declare @.myDate datetime set @.myDate = getdate()

select dateName (dw, @.myDate) + ', ' +
dateName (month, @.myDate) + ' ' +
convert (varchar (2), datepart (dd, @.myDate)) + ', ' +
convert (varchar (4), year(@.myDate)) as [Date Name String]

--
-- S A M P L E O U T P U T :
--

-- Date Name String
-- -
-- Wednesday, November 8, 2006

|||Formatting of dates should be preferable done in the frontent not in the backend.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Mookey wrote:

I currently get a date in the default format in my stored procedure. Up until now converting the date to type 101 was acceptable. Now they want the date to read something like Wednesday, November 8, 2006 or November 8, 2006. How do I do this convert in a single select statement?

I found the types that add the time but I have to make sure that the time isn't included for merge reasons.

Help

Jeff

SELECT CAST(DATENAME(MONTH, GETDATE()) AS varchar(15)) + ' ' + CAST(DATEPART(DAY, GETDATE()) AS varchar(2)) + ', ' + CAST(DATEPART(YEAR, GETDATE()) AS varchar(4))

Adamus

|||

I second what Jens states (unless you are doing a one time data conversion using SQL, then follow the other ideas :).

Leave the formatting of dates (and any values) to the presentation layer. There should be some common object that they reuse for all date values that are returned in the native SQL Server date datatype, which is not actually formatted any way. SSMS uses a standard format to display dates, which is the best format for passing dates, but clearly not what the average person want to see in the UI. Then the formatting of dates can be tied to the machines formatting, or controlled via a configuration that all users use.

date prolem

I have created a table in which one column is datetime data type. But its
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect o
f
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function
.
John
"Rajani" wrote:

> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dat
es
> in dd-mon-yyyy?
> thanks in advance|||
> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...[vbcol=seagreen]
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>|||Ooopps!
"Uri Dimant" wrote:

>
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
>
>sql

date prolem

I have created a table in which one column is datetime data type. But its
storing date andtimetogether. How can i chane the format to store only dates
in dd-mon-yyyy?
thanks in advanceHi
CREATE TABLE #Test (dt DATETIME)
#1
INSERT INTO #Test SELECT CONVERT(VARCHAR(15),GETDATE(),112)
#2
INSERT INTO #Test SELECT CAST(FLOOR(CAST(GETDATE()+1 AS FLOAT)) AS DATETIME)
SELECT * FROM #Test
"Rajani" <Rajani@.discussions.microsoft.com> wrote in message
news:37A7B0A2-F126-402D-A0B1-1D70009C45CA@.microsoft.com...
>I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only
> dates
> in dd-mon-yyyy?
> thanks in advance|||Hi
To expand on Hugo's post. The SQL Server datetime datatype will hold both a
date and time. If you insert a date value with no time portion then the time
will be defaulted to 00:00:00.000 and if you specify only a time the date is
defaulted to
1900-01-01. With SQL Server there is the CONVERT function that will format a
datetime datetype value as a string. This will take a format specifier to
determine what format the string is. The getdate function will return the
current date and time, Hugo's second INSERT statement will use the FLOOR
function to truncate the time portion of a datetime data value, the effect of
adding 1 to getdate will add one day. Therefore his code will add one day to
the current date and time and then truncate the time portion.
See Books Online for more on the datetime data type and the CONVERT function.
John
"Rajani" wrote:
> I have created a table in which one column is datetime data type. But its
> storing date andtimetogether. How can i chane the format to store only dates
> in dd-mon-yyyy?
> thanks in advance|||> To expand on Hugo's post.
:--) sorry may name is Uri
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> Hi
> To expand on Hugo's post. The SQL Server datetime datatype will hold both
> a
> date and time. If you insert a date value with no time portion then the
> time
> will be defaulted to 00:00:00.000 and if you specify only a time the date
> is
> defaulted to
> 1900-01-01. With SQL Server there is the CONVERT function that will format
> a
> datetime datetype value as a string. This will take a format specifier to
> determine what format the string is. The getdate function will return the
> current date and time, Hugo's second INSERT statement will use the FLOOR
> function to truncate the time portion of a datetime data value, the effect
> of
> adding 1 to getdate will add one day. Therefore his code will add one day
> to
> the current date and time and then truncate the time portion.
> See Books Online for more on the datetime data type and the CONVERT
> function.
> John
>
> "Rajani" wrote:
>> I have created a table in which one column is datetime data type. But its
>> storing date andtimetogether. How can i chane the format to store only
>> dates
>> in dd-mon-yyyy?
>> thanks in advance|||Ooopps!
"Uri Dimant" wrote:
> > To expand on Hugo's post.
> :--) sorry may name is Uri
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:63DE3158-5C2F-4598-9CF4-2E9638BBA1C2@.microsoft.com...
> > Hi
> >
> > To expand on Hugo's post. The SQL Server datetime datatype will hold both
> > a
> > date and time. If you insert a date value with no time portion then the
> > time
> > will be defaulted to 00:00:00.000 and if you specify only a time the date
> > is
> > defaulted to
> > 1900-01-01. With SQL Server there is the CONVERT function that will format
> > a
> > datetime datetype value as a string. This will take a format specifier to
> > determine what format the string is. The getdate function will return the
> > current date and time, Hugo's second INSERT statement will use the FLOOR
> > function to truncate the time portion of a datetime data value, the effect
> > of
> > adding 1 to getdate will add one day. Therefore his code will add one day
> > to
> > the current date and time and then truncate the time portion.
> >
> > See Books Online for more on the datetime data type and the CONVERT
> > function.
> >
> > John
> >
> >
> > "Rajani" wrote:
> >
> >> I have created a table in which one column is datetime data type. But its
> >> storing date andtimetogether. How can i chane the format to store only
> >> dates
> >> in dd-mon-yyyy?
> >>
> >> thanks in advance
>
>

Date Problem

I have a problem with date parameter. I have selected data type as date time. I would like the date to be in British format. When I select a date from Calendar control, for example 25 December, then I get a error>

The value provided for the report prameter Startdate is not valid for its type.

Can anyone please help me on this?

regards

Josh

Hello JoshKer.

In Reporting Servies you can define the default Language of the reports, this Language is used for the Date Culture...

To edit him : In Layout Tab / Click on the Yello Part (out of the report) / Properties or F4 / then you accessed the Report Properties...

Alter the Language Propertie, to English (United KingDown)

If this not correct your error, then you can make the CAST on the Date Parameter on the Query....

I Hope Have helped you !

|||See if the problem is dedicated to this entry, if yes, vote for it.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=271928

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thanks for your reply. But it gives the same problem with the date picker.

Date picker parameter with MDX...

I am sure others have ran into this issue.

But I need to have date parameters using the datetime type and I need to be able to pass the correct value to the MDX.

For those of you that are interested you merely need to format the parameter values coming into your dataset your passing the report params to.

Ex.

Code Snippet

="[COE Date].[Date].&["&Format(Parameters!FromCOEDateDate.Value,"yyyy-MM-dd")+"T00:00:00]"

The above example shows the FromCOEDateDate parameter used in the report

Date Picker on Report Parameters

Is there a way to configure a Date Picker option on a report date type
parameter. The report parameter ("From Date") is defined as a date.
Thanks
--
Mike RottmannNo date picker in RS 2000, there is a date picker in RS 2005
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike" <Mrottmann@.micorsoft.com> wrote in message
news:D6D9528D-7CB5-4CBB-8DA8-9C1ACE6EA069@.microsoft.com...
> Is there a way to configure a Date Picker option on a report date type
> parameter. The report parameter ("From Date") is defined as a date.
> Thanks
> --
> Mike Rottmann|||Thanks.
Mike Rottmann
"Bruce L-C [MVP]" wrote:
> No date picker in RS 2000, there is a date picker in RS 2005
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike" <Mrottmann@.micorsoft.com> wrote in message
> news:D6D9528D-7CB5-4CBB-8DA8-9C1ACE6EA069@.microsoft.com...
> > Is there a way to configure a Date Picker option on a report date type
> > parameter. The report parameter ("From Date") is defined as a date.
> >
> > Thanks
> > --
> > Mike Rottmann
>
>

Date Parameter without time

In the Report Parameters dialog I define my Parameter and set
Data Type to Datetime.
When I run report I get 22/2/2005 12:00:00 AM in parameter box on
top of report (parameters section of report), which is correct.
But I want to format this to appear and make input like 22/2/2005
ie.(dd/MM/yyyy) and without time part.
Thanks in advanceI use a string for all of my dates and it works fine. In my dataset or
SP that is called, I simply format the date and add 00:00:00 to the
start date, and 23:59:59 to the end date.|||Or you could use a custom code to return a short date string for that
field.
call Code.FormatDate(Fields!data.Value)
Function FormatDate(ByVal date as Object) as String
If IsDate(data) Then
Return CDate(date).ToShortDateString()
Else
Return String.Empty
End If
End Funtion

Date Parameter ERROR

My RS has two date parameters setup as a date data type. If the user was to
key in 01102004 instead of 01/10/2004 they get this error;
Reporting Services Error
"The value provided for the report parameter 'P_FromDate' is not valid for
its type. (rsReportParameterTypeMismatch)"
How can set that parameter up so I can enter in either of the above dates
formats? I need to beable to do error handling on these two fields.Have the parameter be of string type and then base your query on a dynamic
sql (an expression). The expression can call code that parses the date. But
if it is a bad date it gets tricky to give an error message. To have more
control of the parameters you would need to have your own asp page that is
used to get the parameters and then use either URL integration or web
services to integrate with RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44D70CDD-0EF9-4BA1-82F0-ADFD748E45D8@.microsoft.com...
> My RS has two date parameters setup as a date data type. If the user was
to
> key in 01102004 instead of 01/10/2004 they get this error;
> Reporting Services Error
> "The value provided for the report parameter 'P_FromDate' is not valid for
> its type. (rsReportParameterTypeMismatch)"
> How can set that parameter up so I can enter in either of the above dates
> formats? I need to beable to do error handling on these two fields.

date parameter

One of the fields in the table is of type smalldatetime.
When I select a date from the report parameter (a calender), the report produces an error: The value provided for the report parameter 'ValueDate' is not valid for its type.
Please note that if I use a report parameter of type string and enter 21 may 2007 the report works. But if a date is selected from the calender control i.e. 21/05/2007 the report produces the above error.

How is it possible to use the calender parameter without the report giving the error pls?
Thanks

i think u should go to report parameter ->properties -> change datatype to datetime|||

Hi,
Yes I am doing just that.
That is the problem because even though the parameter is set to datetime, it does not accept the format such as 21/05/2007 which is what you get if the date is selected from the calender.

Thanks

|||

Hi there,

Have ever you tried to fill a data in other format? This error semms like a invalid format date. Try to fill 05/21/2007 (English format - mm/dd/yyyy).

Let me know if this solved your problem.

Lant

|||you have to pass it in mm/dd/yyyy format

Date parameter

My report has a StartDate and a EndDate parameters. I use the datetime
parameter type, and the input textbox shows both the date and time portions.
How to show only the date portion in the parameter textbox? By the way, is it
possible to have something like a datetime picker to get the date parameter?
The textbox control is not user-friendly.When you specify the parameter as date time, both with display although the
user may enter only the date or the time, with the remaing defaulting to the
min...
Some folks will use a string parameter instead...
You could populate the dates from a table in the database... But using a
date picker, you'd have to put an html page in front of the report and
include the date picker in that, then call the report from a web service.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Matthew Chow" <Matthew Chow@.discussions.microsoft.com> wrote in message
news:025C4D51-7562-4532-ACE0-C5C39DF55D3E@.microsoft.com...
> My report has a StartDate and a EndDate parameters. I use the datetime
> parameter type, and the input textbox shows both the date and time
portions.
> How to show only the date portion in the parameter textbox? By the way, is
it
> possible to have something like a datetime picker to get the date
parameter?
> The textbox control is not user-friendly.|||On Mon, 24 Jan 2005 07:47:02 -0800, Matthew Chow <Matthew
Chow@.discussions.microsoft.com> wrote:
>My report has a StartDate and a EndDate parameters. I use the datetime
>parameter type, and the input textbox shows both the date and time portions.
>How to show only the date portion in the parameter textbox? By the way, is it
>possible to have something like a datetime picker to get the date parameter?
>The textbox control is not user-friendly.
DatePicker is rather "too much" to ask :))
As of date parameters, to avoid confusion among end-users with all
that extra time info, just use a string parameter with a prompt like
"Start Date (mm/dd/yyyy):" The proposed format should match the
locale of your server, so even foreign users (if you have any) won't
make mistakes. Then in your query use somethig like this:
WHERE MyTable.MyDate >= CONVERT(datetime, @.pStartDate)
If they enter something invalid, they will get a SQL error, like
"unable to convert to a datetime". This approach is less efficient,
because user's input is being validated on the database level, not on
the web page level, but at the same time provides a much cleaner way
for the users by avoiding confusion. We are now using this everywhere.|||Thanks for helping.sql

Date output to flat file

I am using a simple input from an SQL data base where I have 4 dates defined as type D. I am writing to a flat file with the fileds defined with any available date format and the output on the flat file comes out as "mm/dd/yy 00:00". I'd like to just have the date portion with no time. The input does not have a time on it so I understand the 00:00 as the value. It seems that I shouldn't have to do any extra work as it is date to date. I've seen the gyrations for a date from the SQL database when it is a character field, but that's not the issue here.

Thanks!

how does your sql data looks like? mm/dd/yyyy hh:mis?

if you are using a data flow task, you may want to add data conversion transformation to it, to convert to your required format.

|||In your source, use a SQL statement to retrieve the records. For the four date fields, use the following code:

CONVERT(varchar(20), yourDateField, 101)|||The fields are not character - they are defined as date fields in the SQL table.|||It is shown on the table layout as a "D" type with 0 length.|||That is why Phil asked you to convert into varchar with your required format in sql command.

Thanks|||

JLBSYS wrote:

The fields are not character - they are defined as date fields in the SQL table.

Yeah, I know. This converts them to character strings. Try it in management studio to see what it does.

OR your other option is to take what you've currently got built and add a derived column to create a new column that casts the date/time fields to DT_DBDATE fields.|||

This is what I did since I had to work a few other fields also. I was just wondering why you had to go through gyrations to get an input date to an output date. Since you can select the format on the ADVANCE option of the output connection manager for the Flat file. Changing the format does not change the output.

Thanks everyone for quick response!

Date Only Data Type

I would like to have a field in a table that is a Date only data type instea
d
of the datetime. Could someone offer any advice on this in SQL Server 2005.
ThanksDoesn't exists
You could store YYYYMMDD in a char(8) field instead
http://sqlservercode.blogspot.com/|||No such thing.
http://www.aspfaq.com/2206
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||>> I would like to have a field [sic] in a table that is a DATE only data type in
stead of the DATETIME. Could someone offer any advice on this in SQL Serve
r 2005. <<
You might want to learn the basics of RDBMS, so that you do not confuse
fields and columns.
Now, to answer your question: do it the right way! Time is not a point
(Chronons), but a duration expressed as half-open intervals. Thus a
day is really "[yyyy-mm-dd 00:00:00, yyyy-mm-dd 23:59:59.999..)" in the
ISO temporal model.
Read Rick Snodgrass at Univeristy of AZ for more details.|||If you want to treat the data as a date, without a time value, just ignore
the time value when your application updates or selects from the database.
The only time it makes a difference is if you are comparing two date values.
If you always insert/update your date values without specifying the time,
then the time is set to midnight of that day. As long as you are
consistent, this has the same effect as if you didn't store the time at all,
and you wont run into issues when comparing dates.
You could build in a trigger that insures the time value is set to 00:00:00
on inserts and updates as well, which would guarantee consistency even if
the application programming mistakenly sets the time to another value.
The only other real difference is storage, and I doubt it is significant
enough to even give consideration to with today's storage costs.
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
> I would like to have a field in a table that is a Date only data type
instead
> of the datetime. Could someone offer any advice on this in SQL Server
2005.
> Thanks
>|||> The only other real difference is storage, and I doubt it is significant
> enough to even give consideration to with today's storage costs.
Well, I would suggest that if you only care about the date, then using
SMALLDATETIME will minimize storage requirements and make indexes more
efficient, and I don't know of any downside...
A|||I would agree with others that a Date only type has only marginal value as
you could treat all datetimes as 00:00:00.000 time to get the same result.
However, a Date only type can be mentally easier to deal with as you know
time can not effect what your doing. I actually added a TDate and TTime
UDTs to a sql project I did. Can use it as is or update it as needed if you
don't like the api choices. Get the project at link below.
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I would agree with others that a Date only type has only marginal value as
I don't think so. People have been screaming for this for many years,
and I would very disappointed if it is not in the next version of SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||With 2005, they don't have to wait, unless they want to. I agree it has
some value, but maybe I missed a whole bunch of use cases. What are some of
the more profound use cases for Date only you have come across? TIA Erland.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974953149317Yazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I don't think so. People have been screaming for this for many years,
> and I would very disappointed if it is not in the next version of SQL
> Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> With 2005, they don't have to wait, unless they want to.
Why? The Date and Time datatypes were dropped from the product very early
on. I think most of it was due to the volume with which we complained about
its implementation (and by we I do mean myself, Erland and others). I'm
sure that's not what you mean, so while, yes, you could create your own UDT,
have fun with that, and let us know when you have something marginally
useful! The most complex UDT I've seen to date that was actually useable
was POINT. There is so much involved with date validation and
interoperability that it is unlikely you would be able to develop something
that would seamlessly integrate with the rest of the product (most notably
implicit conversion to/from datetime and acceptance as inputs to functions
like DATEDIFF/DATEADD/YEAR/MONTH/DAY).

> What are some of the more profound use cases for Date only you have come
> across?
A calendar table. Hire/fire date. Birth date. I'm sure if I spent more
than two minutes and reviewed all of the projects I've been involved with in
the past 10 years, I could come up with dozens of others. None of these
need time in most cases, and life would be much simpler if we didn't have to
truncate/validate/correct data going in or coming out, or when comparing, or
when displaying, or when exporting to XML, or ...

Date Lookups

I want to do a lookup on date column. My lookup date is of type smalldatetime, and my date is of type datetime (date with time component). My lookup is failing because of incompatible data types.

How do I perform the lookup with date columns having date and time components?

Switch to your source component to use a query and then use the cast operator to cast the datetime column to smalldatetime in the query of you source component.

Rafael Salas

Date in Europe format.

In my table in SQL Server my date column is the data type of datetime. One of the row is 2003-06-17 00:00:00.000. I have to display in my reports the date as 17/06/2003. What will be the syntax I will need to use?
Select (datepart(day,ReturnByDt) + '/ ' + datepart(month,ReturnByDt)+ '/'+ datepart(year,ReturnByDt)) from tblA

I get an error message:
Syntax error converting the varchar value '/ ' to a column of data type int.

Any ideas??
Thanks
SriDates are stored in the database as numbers...you need to pick your format.

Look up convert

oh, and here

SELECT CONVERT(varchar(10),Getdate(),103)|||Select cast(datepart(day,@.ReturnByDt) as varchar(5)) + '/' + cast(datepart(month,@.ReturnByDt) as varchar(5))+ '/'+ cast(datepart(year,@.ReturnByDt) as varchar(5))

Originally posted by sri2003
In my table in SQL Server my date column is the data type of datetime. One of the row is 2003-06-17 00:00:00.000. I have to display in my reports the date as 17/06/2003. What will be the syntax I will need to use?
Select (datepart(day,ReturnByDt) + '/ ' + datepart(month,ReturnByDt)+ '/'+ datepart(year,ReturnByDt)) from tblA

I get an error message:
Syntax error converting the varchar value '/ ' to a column of data type int.

Any ideas??
Thanks
Sri|||Brett's idea is better. Ignore mine.|||Brett..Thanks a million for your quick reply.

Originally posted by Brett Kaiser
Dates are stored in the database as numbers...you need to pick your format.

Look up convert

oh, and here

SELECT CONVERT(varchar(10),Getdate(),103)|||Sorry was not clear....Your syntax worked perfect for me.
Originally posted by sri2003
Brett..Thanks a million for your quick reply.

Date help

Hi -
I have a column in a table that is supposed to be a date, it's data type is
text since it's source is a text file. Which looks like this:
Chg_Date
19990105
20000323
20010526
00000000
00000000
as you can see, there are some dates with 0's which of course, are invalid,
I need to insert this date field in another table even if the date is all
0's, because there is not always a chg_date associated with a record.
Here is what I have tried since I know the 0's are invalid:
select chg_date
from table
where ISDATE(chg_date ) <> 0
and of course, I only get the records that are not 0's - but how do I get
all of the dates whether they are valid or not?Patrice,
Try using a reference date for the 0's. In SQL Server, integer values can be
converted to datetime data type, so 0 (zero) will be converted to 01/01/1900
(19000101).
insert into t2(c1)
select case when isdate(c1) then c1 else '19000101' end
from t1
go
AMB
"Patrice" wrote:

> Hi -
> I have a column in a table that is supposed to be a date, it's data type i
s
> text since it's source is a text file. Which looks like this:
> Chg_Date
> 19990105
> 20000323
> 20010526
> 00000000
> 00000000
> as you can see, there are some dates with 0's which of course, are invalid
,
> I need to insert this date field in another table even if the date is all
> 0's, because there is not always a chg_date associated with a record.
> Here is what I have tried since I know the 0's are invalid:
> select chg_date
> from table
> where ISDATE(chg_date ) <> 0
> and of course, I only get the records that are not 0's - but how do I get
> all of the dates whether they are valid or not?|||You want to make the distinction in your select clause, not your where
clause. That was you are still getting the rows. Putting it in the where
clause is for filtering rows. Manipulate your select clause to work with
columns.
SELECT CASE WHEN ISDATE(chg_date) <> 0 THEN chg_date ELSE NULL END
FROM [table]
HTH,
John Scragg
"Patrice" wrote:

> Hi -
> I have a column in a table that is supposed to be a date, it's data type i
s
> text since it's source is a text file. Which looks like this:
> Chg_Date
> 19990105
> 20000323
> 20010526
> 00000000
> 00000000
> as you can see, there are some dates with 0's which of course, are invalid
,
> I need to insert this date field in another table even if the date is all
> 0's, because there is not always a chg_date associated with a record.
> Here is what I have tried since I know the 0's are invalid:
> select chg_date
> from table
> where ISDATE(chg_date ) <> 0
> and of course, I only get the records that are not 0's - but how do I get
> all of the dates whether they are valid or not?|||Correction:
insert into t2(c1)
select case when isdate(c1) = 1 then c1 else '19000101' end
from t1
go
AMB
"Alejandro Mesa" wrote:
> Patrice,
> Try using a reference date for the 0's. In SQL Server, integer values can
be
> converted to datetime data type, so 0 (zero) will be converted to 01/01/19
00
> (19000101).
> insert into t2(c1)
> select case when isdate(c1) then c1 else '19000101' end
> from t1
> go
>
> AMB
> "Patrice" wrote:
>

Date formatting - Really newbie question

I have a unbelievably stupid problem that I can't figure out. My table is
imported from access and contains a column with data type datetime. I want
to be able to sum the data (which is a door counter for our store) to show
me all of the traffic for the day, then display the date as 1/1/2007 instead
of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
BOL help on this topic)...
convert(smalldatetime, [Date], 101)
No matter what I put in the style portion of the select statement it has no
impact on my output format. I suppose I could convert this to a varchar and
trim the results but that seems to be overkill (in addition to beiing a poor
solution).
Solved this. If anyone else is as inexperienced as I am and has this
problem, you can use the following method to change the output of the
datetime in this method.
Select CONVERT(varchar(20), [Date] as DT
will change this
1/30/2007 10:00:00
to this
1/30/2007
"Chuck G." wrote:

> I have a unbelievably stupid problem that I can't figure out. My table is
> imported from access and contains a column with data type datetime. I want
> to be able to sum the data (which is a door counter for our store) to show
> me all of the traffic for the day, then display the date as 1/1/2007 instead
> of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
> BOL help on this topic)...
> convert(smalldatetime, [Date], 101)
> No matter what I put in the style portion of the select statement it has no
> impact on my output format. I suppose I could convert this to a varchar and
> trim the results but that seems to be overkill (in addition to beiing a poor
> solution).
>
|||I don't quite see how that works (20 is too long, and the is only one
parenthesis). To
convert a datetime value to the same date at midnight, this is one solution:
dateadd(day,datediff(day,0,[Date]),0)
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Chuck G. wrote:
[vbcol=seagreen]
>Solved this. If anyone else is as inexperienced as I am and has this
>problem, you can use the following method to change the output of the
>datetime in this method.
>Select CONVERT(varchar(20), [Date] as DT
>will change this
>1/30/2007 10:00:00
>to this
>1/30/2007
>"Chuck G." wrote:
>

Date formatting

Can some one help me with the syntax to change the datetime data type to the
following format:
mmm yy
Example: Feb 05
Thanks= Format(Fields!myDateField.Value,"dd MMM")
"anthonysjo" wrote:
> Can some one help me with the syntax to change the datetime data type to the
> following format:
> mmm yy
> Example: Feb 05
> Thanks|||Can this syntax only be used in SRS or can I use it when writing queries in
query analyzer or when building views?
"Andre" wrote:
> = Format(Fields!myDateField.Value,"dd MMM")
> "anthonysjo" wrote:
> > Can some one help me with the syntax to change the datetime data type to the
> > following format:
> > mmm yy
> > Example: Feb 05
> >
> > Thanks|||For SQL I usually use syntax like this, but the datatype is not datetime
anymore:
Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
OR
Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
datename(yy,myDate) from MyTable
Andre
"anthonysjo" wrote:
> Can this syntax only be used in SRS or can I use it when writing queries in
> query analyzer or when building views?
> "Andre" wrote:
> > = Format(Fields!myDateField.Value,"dd MMM")
> >
> > "anthonysjo" wrote:
> >
> > > Can some one help me with the syntax to change the datetime data type to the
> > > following format:
> > > mmm yy
> > > Example: Feb 05
> > >
> > > Thanks|||One last question...with mm the month comes back as January. Can I get it
to display just Jan?
"Andre" wrote:
> For SQL I usually use syntax like this, but the datatype is not datetime
> anymore:
> Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
> OR
> Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
> datename(yy,myDate) from MyTable
> Andre
>
> "anthonysjo" wrote:
> > Can this syntax only be used in SRS or can I use it when writing queries in
> > query analyzer or when building views?
> >
> > "Andre" wrote:
> >
> > > = Format(Fields!myDateField.Value,"dd MMM")
> > >
> > > "anthonysjo" wrote:
> > >
> > > > Can some one help me with the syntax to change the datetime data type to the
> > > > following format:
> > > > mmm yy
> > > > Example: Feb 05
> > > >
> > > > Thanks|||You might also try
select convert(char(6),getdate(),107)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
> For SQL I usually use syntax like this, but the datatype is not datetime
> anymore:
> Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
> OR
> Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
> datename(yy,myDate) from MyTable
> Andre
>
> "anthonysjo" wrote:
>> Can this syntax only be used in SRS or can I use it when writing queries
>> in
>> query analyzer or when building views?
>> "Andre" wrote:
>> > = Format(Fields!myDateField.Value,"dd MMM")
>> >
>> > "anthonysjo" wrote:
>> >
>> > > Can some one help me with the syntax to change the datetime data type
>> > > to the
>> > > following format:
>> > > mmm yy
>> > > Example: Feb 05
>> > >
>> > > Thanks|||Where do I define the field I want to use in this example?
select convert(char(6),getdate(),107)
"Wayne Snyder" wrote:
> You might also try
> select convert(char(6),getdate(),107)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
> > For SQL I usually use syntax like this, but the datatype is not datetime
> > anymore:
> >
> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
> > OR
> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
> > datename(yy,myDate) from MyTable
> >
> > Andre
> >
> >
> > "anthonysjo" wrote:
> >
> >> Can this syntax only be used in SRS or can I use it when writing queries
> >> in
> >> query analyzer or when building views?
> >>
> >> "Andre" wrote:
> >>
> >> > = Format(Fields!myDateField.Value,"dd MMM")
> >> >
> >> > "anthonysjo" wrote:
> >> >
> >> > > Can some one help me with the syntax to change the datetime data type
> >> > > to the
> >> > > following format:
> >> > > mmm yy
> >> > > Example: Feb 05
> >> > >
> >> > > Thanks
>
>|||replace the getdate().
select convert(char(6),somefield,107) from sometable
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:FD07C54C-0108-42EF-A32D-908BC5D1903E@.microsoft.com...
> Where do I define the field I want to use in this example?
> select convert(char(6),getdate(),107)
> "Wayne Snyder" wrote:
>> You might also try
>> select convert(char(6),getdate(),107)
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> (Please respond only to the newsgroup.)
>> I support the Professional Association for SQL Server ( PASS) and it's
>> community of SQL Professionals.
>> "Andre" <Andre@.discussions.microsoft.com> wrote in message
>> news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
>> > For SQL I usually use syntax like this, but the datatype is not
>> > datetime
>> > anymore:
>> >
>> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
>> > OR
>> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
>> > datename(yy,myDate) from MyTable
>> >
>> > Andre
>> >
>> >
>> > "anthonysjo" wrote:
>> >
>> >> Can this syntax only be used in SRS or can I use it when writing
>> >> queries
>> >> in
>> >> query analyzer or when building views?
>> >>
>> >> "Andre" wrote:
>> >>
>> >> > = Format(Fields!myDateField.Value,"dd MMM")
>> >> >
>> >> > "anthonysjo" wrote:
>> >> >
>> >> > > Can some one help me with the syntax to change the datetime data
>> >> > > type
>> >> > > to the
>> >> > > following format:
>> >> > > mmm yy
>> >> > > Example: Feb 05
>> >> > >
>> >> > > Thanks
>>|||Ok that getst the Month format correct but the year is wrong now...I get
things like Jan 01, Jan 08, Jan 15, Jan 22, Jan 29, etc...which are the weeks
for which each period closes. How do I get it to come out as 3char month and
year Like Jan 05?
convert(char(6), [EP].[PRD_FINISH_DATE],107)AS [Month]
"Bruce L-C [MVP]" wrote:
> replace the getdate().
> select convert(char(6),somefield,107) from sometable
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:FD07C54C-0108-42EF-A32D-908BC5D1903E@.microsoft.com...
> > Where do I define the field I want to use in this example?
> > select convert(char(6),getdate(),107)
> >
> > "Wayne Snyder" wrote:
> >
> >> You might also try
> >>
> >> select convert(char(6),getdate(),107)
> >>
> >> --
> >> Wayne Snyder MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> (Please respond only to the newsgroup.)
> >>
> >> I support the Professional Association for SQL Server ( PASS) and it's
> >> community of SQL Professionals.
> >> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> >> news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
> >> > For SQL I usually use syntax like this, but the datatype is not
> >> > datetime
> >> > anymore:
> >> >
> >> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
> >> > OR
> >> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
> >> > datename(yy,myDate) from MyTable
> >> >
> >> > Andre
> >> >
> >> >
> >> > "anthonysjo" wrote:
> >> >
> >> >> Can this syntax only be used in SRS or can I use it when writing
> >> >> queries
> >> >> in
> >> >> query analyzer or when building views?
> >> >>
> >> >> "Andre" wrote:
> >> >>
> >> >> > = Format(Fields!myDateField.Value,"dd MMM")
> >> >> >
> >> >> > "anthonysjo" wrote:
> >> >> >
> >> >> > > Can some one help me with the syntax to change the datetime data
> >> >> > > type
> >> >> > > to the
> >> >> > > following format:
> >> >> > > mmm yy
> >> >> > > Example: Feb 05
> >> >> > >
> >> >> > > Thanks
> >>
> >>
> >>
>
>|||Look in books online for SQL Server. It provides all your different options
for convert (there are a lot of options).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:8C98B402-8A2F-4DCA-AE62-6985F5496FE6@.microsoft.com...
> Ok that getst the Month format correct but the year is wrong now...I get
> things like Jan 01, Jan 08, Jan 15, Jan 22, Jan 29, etc...which are the
> weeks
> for which each period closes. How do I get it to come out as 3char month
> and
> year Like Jan 05?
> convert(char(6), [EP].[PRD_FINISH_DATE],107)AS [Month]
> "Bruce L-C [MVP]" wrote:
>> replace the getdate().
>> select convert(char(6),somefield,107) from sometable
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
>> news:FD07C54C-0108-42EF-A32D-908BC5D1903E@.microsoft.com...
>> > Where do I define the field I want to use in this example?
>> > select convert(char(6),getdate(),107)
>> >
>> > "Wayne Snyder" wrote:
>> >
>> >> You might also try
>> >>
>> >> select convert(char(6),getdate(),107)
>> >>
>> >> --
>> >> Wayne Snyder MCDBA, SQL Server MVP
>> >> Mariner, Charlotte, NC
>> >> (Please respond only to the newsgroup.)
>> >>
>> >> I support the Professional Association for SQL Server ( PASS) and it's
>> >> community of SQL Professionals.
>> >> "Andre" <Andre@.discussions.microsoft.com> wrote in message
>> >> news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
>> >> > For SQL I usually use syntax like this, but the datatype is not
>> >> > datetime
>> >> > anymore:
>> >> >
>> >> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
>> >> > OR
>> >> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
>> >> > datename(yy,myDate) from MyTable
>> >> >
>> >> > Andre
>> >> >
>> >> >
>> >> > "anthonysjo" wrote:
>> >> >
>> >> >> Can this syntax only be used in SRS or can I use it when writing
>> >> >> queries
>> >> >> in
>> >> >> query analyzer or when building views?
>> >> >>
>> >> >> "Andre" wrote:
>> >> >>
>> >> >> > = Format(Fields!myDateField.Value,"dd MMM")
>> >> >> >
>> >> >> > "anthonysjo" wrote:
>> >> >> >
>> >> >> > > Can some one help me with the syntax to change the datetime
>> >> >> > > data
>> >> >> > > type
>> >> >> > > to the
>> >> >> > > following format:
>> >> >> > > mmm yy
>> >> >> > > Example: Feb 05
>> >> >> > >
>> >> >> > > Thanks
>> >>
>> >>
>> >>
>>

Date format with SQL Server 2000

Hi,

I'm working with a table with more than 2 million rows. The problem is that the table has a field called "ShipDate" of type nvarchar. When I try to change the data type to DateTime sql server throws a data conversion error.

I noticed that the date format is as follow: 2006-10-23, so, is there a way sql server takes this field and change it using the field values and applying the correct format? meaning: 10/23/2006

thanks a lot

The mentioned value should be easily converted into a datetime column (2006-10-23). You seem to have corrupt data stored in the datecolumn. Try to get rid of it and prior identify it by using the following query:

SELECT Datecolumn
FROM SomeTable
WHERE ISDATE(Datecolumn) = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

date format transfer

Hi,
My data type of date field is smallint,
I select it and get 2004-08-16 00:00:00:000
How could I modify it as 93/08/16 (Chinese year)
(93 is Chiense year, 2004 - 1911 = 93)
Thanks!
AngiSELECT CONVERT(CHAR(8), DATEADD(YEAR, -11, column), 11) FROM table
http://www.aspfaq.com/
(Reverse address to reply.)
"Angi" <angi@.microsoft.com> wrote in message
news:O6NtrG6gEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> My data type of date field is smallint,
> I select it and get 2004-08-16 00:00:00:000
> How could I modify it as 93/08/16 (Chinese year)
> (93 is Chiense year, 2004 - 1911 = 93)
> Thanks!
> Angi
>