How to show result for each day of the past 30 days?

Hello,

I would like to show the result fo each day of the past 30 days. I might not have data to display for everyday but I still want the date to show up
01/27/2007 resultA
01/28/2007 resultB
01/29/2007 .
...
02/26/2007 resultC
02/27/2007 resultD

Thanks for your help
Arnold

If there is a possibility there could be no records for each day in the column, you might have to build this table manually. If the records do exist you can do a GROUP BY convert(Varchar,datefield, 101). You can probably create a stored proc , use a table variable with the 2 columns and insert data into it and finally do a SELECT from it.|||

Could you give me an example for the table variable as I have no idea on how to build that.

Thanks

|||

Just to give you an idea here's a sample. You might need to build your code on top of this (if this works for you).

declare @.ttable( Datecoldatetime, col2varchar(50))Declare @.mindatedatetime, @.maxDatedatetime, @.daysint,Set @.mindate ='01/27/2007'Set @.maxDate ='02/27/2007'while (datediff(d,@.mindate,@.maxdate) >= 0 )BeginInsert into @.t (Datecol, col2)Values (@.mindate )set @.mindate = @.mindate + 1--Another Query to get the results for date = @.mindateEndselect *from @.t

How to Show Last Admit Date

I have a report that shows patient information, such as patient number, name, and admit date. It also has a date parameter. I enter in the date and it shows all the patients admitted that day. I would like to show the last date the patient was admitted before the date I ran the report for.

For example, if I run the report for 12/20/06, I want to see the patients that were admitted that day and their info like name, and patient number. I would also like to see the last date they were admitted.

Could you just do a subquery to get the max date for each patient from before the date parameter? Something like this:

select ...
(select max(admit_date) from patient_admittances pa where p.patient_id = pa.patient_id and admit_date < @.RunDate)
from patients p
inner join ...

Jarret

How to set this parameter AlwaysUseDefaultCodePath="TRUE"

Hello all,
I am trying to migrate date from Oracle 10g to SQL serve 2005 during the data transformation I get the following error

Messages

Warning 0x80202066: Source - SERVICE [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)

I searched internet and solution seems to be setting AlwaysUseDefaultCodePath="TRUE"

But where do you do this ?

I found this too: It's on the Properties tab of the OLE DB Source in Custom Properties section.

But still do not know where to go to set this parameter

Please help

The AlwaysUseDefaultCodePath property may be available in the Advanced Editor for the OLE DB Source. This editor has a Component Properties tab, and Input and Output Properties tab.

To open the Advanced Editor, right-click the OLE DB Source component in the package, and then click Advanced Editor.

|||For thi styoe of property I normally find it faster and easier to just use the Properties window in the designer, rather than opening the Editor. Select the component, and then hit F4 to bring the properties window into focus if it is not already.|||Thanks, Thats what I was looking for, it resolve my issue

How to set this parameter AlwaysUseDefaultCodePath="TRUE"

Hello all,
I am trying to migrate date from Oracle 10g to SQL serve 2005 during the data transformation I get the following error

Messages

Warning 0x80202066: Source - SERVICE [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)

I searched internet and solution seems to be setting AlwaysUseDefaultCodePath="TRUE"

But where do you do this ?

I found this too: It's on the Properties tab of the OLE DB Source in Custom Properties section.

But still do not know where to go to set this parameter

Please help

The AlwaysUseDefaultCodePath property may be available in the Advanced Editor for the OLE DB Source. This editor has a Component Properties tab, and Input and Output Properties tab.

To open the Advanced Editor, right-click the OLE DB Source component in the package, and then click Advanced Editor.

|||For thi styoe of property I normally find it faster and easier to just use the Properties window in the designer, rather than opening the Editor. Select the component, and then hit F4 to bring the properties window into focus if it is not already.|||Thanks, Thats what I was looking for, it resolve my issue

How to set this parameter AlwaysUseDefaultCodePath="TRUE"

Hello all,
I am trying to migrate date from Oracle 10g to SQL serve 2005 during the data transformation I get the following error

Messages

Warning 0x80202066: Source - SERVICE [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)

I searched internet and solution seems to be setting AlwaysUseDefaultCodePath="TRUE"

But where do you do this ?

I found this too: It's on the Properties tab of the OLE DB Source in Custom Properties section.

But still do not know where to go to set this parameter

Please help

The AlwaysUseDefaultCodePath property may be available in the Advanced Editor for the OLE DB Source. This editor has a Component Properties tab, and Input and Output Properties tab.

To open the Advanced Editor, right-click the OLE DB Source component in the package, and then click Advanced Editor.

|||For thi styoe of property I normally find it faster and easier to just use the Properties window in the designer, rather than opening the Editor. Select the component, and then hit F4 to bring the properties window into focus if it is not already.|||Thanks, Thats what I was looking for, it resolve my issue

How to set null value for a smalldatetime inside a Script Component task?

how the hell you allocate a null value for a smalldatetime sql field?

Now, I'm putting a false date because of I'm stuck with this f.. and then I do an update:

.Parameters("@.FecEnajenacion").Value = "1999-01-01"

error:

.Parameters("@.FecEnajenacion").Value = vbNull

.Parameters("@.FecEnajenacion").Value = Null

.Parameters("@.FecEnajenacion").Value = SqlDbType.?

Try

Parameters("@.FecEnajenacion").Value = Nothing

-Jamie

|||

hi jamie,

Well, it doesn't works because of my code wait any value:

Public Overrides Sub PreExecute()

sqlCmd = New SqlCommand(sSql, sqlConn)

sqlParam = New SqlParameter("@.FecEnajenacion", SqlDbType.SmallDateTime)

sqlCmd.Parameters.Add(sqlParam)

....

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

.....

With Sqlcmd

If dFecha2 = "0000/00/00" Then

.Parameters("@.FecEnajenacion").Value = Nothing

Else

.Parameters("@.FecEnajenacion").Value = dFecha2

End If

.ExecuteNonQuery()

End With

End Sub

Prepared statement '(@.Ejercicio smallint,@.NIFPerc char(9),@.NIFRep char(9),@.Nombre va' expects parameter @.FecEnajenacion, which was not supplied.

|||enric,

the DBNull data type must be used when setting the parameter value property to null.

i recommend that you post this question to the ADO.NET forum for further assistance.|||thank you

How to set default date in SQL to

somthing other than 1/1/1900? I use Visual Studio.NET and Aspx.
Even if I pass a system.dbnull.value to the table, it keeps showing
a default date of the above. I want to end-up with a <NULL> for the
date entry from my code when a user deletes a date from my
aspx page.

Thanks,You can mix something from:

use a DEFAULT constraint to your date column
use NULLIF() function with an out-of-range date
use ISNULL() function with an out-of-range date
and check that your table supports NULLs in your date column

we also have problems passing nulls via VB6 (must be variants).

Cesar.|||Thanks Cesar,

I saw a KB on Microsoft saying that functionality is a bug but don't see
they fixed it. I will give the ole variant a try. Thanks again.

BobbyJ

How to set default date

Hi Guys,

I have a time standard dimension used as a filter to my report.

How can I set the parameter to get the current month and year to be used by the time standard filter.

Select the report, and go to Report->Parameters, select the parameter, in the default values sectoin, set the value there. You can use function too, like get current date, use =Today, and DatePart() to get year, month, date, whatever.

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

How to set date format as mm/dd/yyyyThh:mm:ss.ttttZ+00:00?

Hi,
I use "For XML" statement to export xml, all are my expected. But the
datetime field value is not my expected format(e.g.
ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?You would have to use CONVERT(nvarchar(40), datetimeval, 127) + N'Z' in the
select clause since SQL Server currently does not know what timezone you
could possibly want.
Best regards
Michael
"ABC" <abc@.abc.com> wrote in message
news:urvzmAilHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use "For XML" statement to export xml, all are my expected. But the
> datetime field value is not my expected format(e.g.
> ModifiedDate="2005-05-16T16:33:33.060"), Can I force the sql server's xml
> date format including timezone like as yyyy/mm/ddThh:mm:ss.ttttZ+00:00?
>
>

How to set date as default

Hi, I probably do something stupid, but I cannot figure out how to set the date as deault when

inserting a record in a table. I have created a Default in the database, giving it a name and the Value = GetDate() and bind it to the desired column.

When I insert a record without filling in a value in this datefield, the table shows me "01/01/1900" in stead of Today.

What am I doing wrong ?

Help is appreciated, Ger.

How are you doing the insert?|||

Here is part of the code :

Dim ConnStr As String = "workstation id=ONTWIKKEL;packet size=4096;integrated security=SSPI;data source='ONTWIKKEL\WEBAPPS';persist security info=False;initial catalog=UPOdyssee"
Dim Conn As SqlClient.SqlConnection = New SqlClient.SqlConnection(ConnStr)
Dim mSQL As String
Dim a(99)
a(1) = Request.Form("HtxtVestiging")
a(2) = Request.Form("HtxtInschrijfdatum")
a(3) = Request.Form("HtxtNaamVoorletters")
a(4) = Request.Form("HtxtRoepnaam")
a(5) = Request.Form("HtxtLoginnaam")
a(6) = Request.Form("HtxtPaswoord")
a(7) = Request.Form("HtxtAdres")
a(8) = Request.Form("HtxtPostcode")
a(9) = Request.Form("HtxtWoonplaats")
a(10) = Request.Form("HtxtTelnr")
a(11) = Request.Form("HtxtEmail")
a(12) = Request.Form("HtxtMobiel")
a(13) = Request.Form("HtxtBereikbaarVia")
a(14) = Request.Form("HtxtGeboortedatum")
a(15) = DDL_Geslacht.SelectedValue
a(16) = Request.Form("HtaWatVoorWerk")
a(17) = DDL_VastTijdelijk.SelectedValue
a(18) = Request.Form("HtxtVanafWelkePeriode")
a(19) = Request.Form("HtxtTotWelkePeriode")
a(20) = DDL_Beschikbaarheid.SelectedValue
a(21) = Request.Form("HtxtVanUren")
a(22) = Request.Form("HtxtTotUren")
a(23) = DDL_Vervoer.SelectedValue
a(24) = DDL_Reisbereidheid.SelectedValue
a(25) = Request.Form("HtxtMaxReistijd")
a(26) = Request.Form("HtxtMaxKM")
a(27) = Request.Form("HtxtInkomenBruto")
a(28) = Request.Form("HtxtInkomenNetto")
a(29) = DDL_Uitkering.SelectedValue
a(30) = Request.Form("HtxtUitkering")
a(31) = Request.Form("HtxtFiscaalnr")
a(32) = DDL_Identificatiebewijs.SelectedValue
a(33) = Request.Form("HtxtSoortLegitimatiebewijs")
a(34) = Request.Form("HtxtLegitimatiebewijsnr")
a(35) = Request.Form("HtxtGeldigTot")
a(36) = DDL_Rijbewijs.SelectedValue
a(37) = Request.Form("HtxtSoortRijbewijs")
a(38) = Request.Form("HtxtRijbewijsnr")
a(39) = DDL_MilitaireDienst.SelectedValue
a(40) = Request.Form("HtxtFunctieMD")
a(41) = Request.Form("HtxtJaarMD")
a(42) = Request.Form("HtxtPaspoortnr")
a(43) = Request.Form("HtxtEinddatumPaspoort")
a(44) = Request.Form("HtxtVergunningnr")
a(45) = Request.Form("HtxtEinddatumVergunning")
a(46) = Request.Form("HtxtBankGiroNr")
a(47) = DDL_Ziektekosten.SelectedValue
a(48) = Request.Form("HtxtPolisnr")
a(49) = DDL_HoeAanOnsGekomen.SelectedValue
a(50) = Request.Form("HtxtOpl1")
a(51) = Request.Form("HtxtOpl2")
a(52) = Request.Form("HtxtOpl3")
a(53) = Request.Form("HtxtOpl4")
a(54) = Request.Form("HtxtOpl5")
a(55) = Request.Form("HtxtOpl6")
a(56) = Request.Form("HtxtOpl7")
a(57) = Request.Form("HtxtRichting1")
a(58) = Request.Form("HtxtRichting2")
a(59) = Request.Form("HtxtRichting3")
a(60) = Request.Form("HtxtRichting4")
a(61) = Request.Form("HtxtRichting5")
a(62) = Request.Form("HtxtRichting6")
a(63) = Request.Form("HtxtRichting7")
a(64) = Request.Form("HtxtDipl1")
a(65) = Request.Form("HtxtDipl2")
a(66) = Request.Form("HtxtDipl3")
a(67) = Request.Form("HtxtDipl4")
a(68) = Request.Form("HtxtDipl5")
a(69) = Request.Form("HtxtDipl6")
a(70) = Request.Form("HtxtDipl7")
a(71) = Request.Form("HtxtJaar1")
a(72) = Request.Form("HtxtJaar2")
a(73) = Request.Form("HtxtJaar3")
a(74) = Request.Form("HtxtJaar4")
a(75) = Request.Form("HtxtJaar5")
a(76) = Request.Form("HtxtJaar6")
a(77) = Request.Form("HtxtJaar7")
a(78) = DDL_NogStudie.SelectedValue
a(79) = Request.Form("HtxtWelkeStudie")
a(80) = Request.Form("HtxtWGVanTot1")
a(81) = Request.Form("HtxtWGVanTot2")
a(82) = Request.Form("HtxtWGVanTot3")
a(83) = Request.Form("HtxtWGVanTot4")
a(84) = Request.Form("HtxtWGVanTot5")
a(85) = Request.Form("HtxtWGVanTot6")
a(86) = Request.Form("HtxtFunctie1")
a(87) = Request.Form("HtxtFunctie2")
a(88) = Request.Form("HtxtFunctie3")
a(89) = Request.Form("HtxtFunctie4")
a(90) = Request.Form("HtxtFunctie5")
a(91) = Request.Form("HtxtFunctie6")
a(92) = Request.Form("HtxtVertrek1")
a(93) = Request.Form("HtxtVertrek2")
a(94) = Request.Form("HtxtVertrek3")
a(95) = Request.Form("HtxtVertrek4")
a(96) = Request.Form("HtxtVertrek5")
a(97) = Request.Form("HtxtVertrek6")
a(98) = Request.Form("HtaBijzOpm")
a(99) = DDL_Functie.SelectedIndex + 1 ' i.v.m. de zerobased van de ddl en de 1-based van de tabel
mSQL = "INSERT into Medewerkers (Vestiging,Inschrijfdatum, Naam, Roepnaam, Loginnaam, Paswoord, "
mSQL = mSQL & "Adres, Postcode, Woonplaats, Telefoonnummer, Email, Mobiel, BereikTel, Geboortedatum, Geslacht, WatVoorWerk, VastTijdelijk, VanafPeriode, TotPeriode, Beschikbaarheid, "
mSQL = mSQL & "VanUren, TotUren, Vervoer, Reisbereidheid, MaxReistijd, MaxKM, InkomenBruto, InkomenNetto, Uitkering, SoortUitkering, Fiscaalnr, "
mSQL = mSQL & "Identificatiebewijs, SoortLegibewijs, Legitimatiebewijs, GeldigTot, Rijbewijs, "
mSQL = mSQL & "Soortrijbewijs, Rijbewijsnr, MilitaireDienst, FunctieMD, JaarMD, Paspoortnr, EinddatumPaspoort, Vergunningnr, EinddatumVergunning, BankGironr, "
mSQL = mSQL & "Ziektekosten, Polisnr, HoeAanOnsGekomen, Opl1, Opl2, Opl3, Opl4, Opl5, Opl6, "
mSQL = mSQL & "Opl7, Richting1, Richting2, Richting3, Richting4, Richting5, Richting6, Richting7, Dipl1, Dipl2, Dipl3, Dipl4, Dipl5, Dipl6, Dipl7, "
mSQL = mSQL & "Jaar1, Jaar2, Jaar3, Jaar4, Jaar5, Jaar6, Jaar7, NogStudie, WelkeStudie, WGVanTot1, WGVanTot2, WGVanTot3, WGVanTot4, WGVanTot5, "
mSQL = mSQL & "WGVanTot6, Functie1, Functie2, Functie3, Functie4, Functie5, Functie6, Vertrek1, Vertrek2, Vertrek3, Vertrek4, "
mSQL = mSQL & "Vertrek5, Vertrek6, BijzOpm, FunctieID) "
mSQL = mSQL & "VALUES ('" & a(1) & "','" &a(2) & "','" & a(3) & "','" & a(4) & "','" & a(5) & "','" & a(6) & "',"
mSQL = mSQL & "'" & a(7) & "','" & a(8) & "','" & a(9) & "','" & a(10) & "','" & a(11) & "','" & a(12) & "',"
mSQL = mSQL & "'" & a(13) & "','" & a(14) & "','" & a(15) & "','" & a(16) & "','" & a(17) & "','" & a(18) & "',"
mSQL = mSQL & "'" & a(19) & "','" & a(20) & "','" & a(21) & "','" & a(22) & "','" & a(23) & "','" & a(24) & "',"
mSQL = mSQL & "'" & a(25) & "','" & a(26) & "','" & a(27) & "','" & a(28) & "','" & a(29) & "','" & a(30) & "',"
mSQL = mSQL & "'" & a(31) & "','" & a(32) & "','" & a(33) & "','" & a(34) & "','" & a(35) & "','" & a(36) & "',"
mSQL = mSQL & "'" & a(37) & "','" & a(38) & "','" & a(39) & "','" & a(40) & "','" & a(41) & "','" & a(42) & "',"
mSQL = mSQL & "'" & a(43) & "','" & a(44) & "','" & a(45) & "','" & a(46) & "','" & a(47) & "','" & a(48) & "',"
mSQL = mSQL & "'" & a(49) & "','" & a(50) & "','" & a(51) & "','" & a(52) & "','" & a(53) & "','" & a(54) & "',"
mSQL = mSQL & "'" & a(55) & "','" & a(56) & "','" & a(57) & "','" & a(58) & "','" & a(59) & "','" & a(60) & "',"
mSQL = mSQL & "'" & a(61) & "','" & a(62) & "','" & a(63) & "','" & a(64) & "','" & a(65) & "','" & a(66) & "',"
mSQL = mSQL & "'" & a(67) & "','" & a(68) & "','" & a(69) & "','" & a(70) & "','" & a(71) & "','" & a(72) & "',"
mSQL = mSQL & "'" & a(73) & "','" & a(74) & "','" & a(75) & "','" & a(76) & "','" & a(77) & "','" & a(78) & "',"
mSQL = mSQL & "'" & a(79) & "','" & a(80) & "','" & a(81) & "','" & a(82) & "','" & a(83) & "','" & a(84) & "',"
mSQL = mSQL & "'" & a(85) & "','" & a(86) & "','" & a(87) & "','" & a(88) & "','" & a(89) & "','" & a(90) & "',"
mSQL = mSQL & "'" & a(91) & "','" & a(92) & "','" & a(93) & "','" & a(94) & "','" & a(95) & "','" & a(96) & "',"
mSQL = mSQL & "'" & a(97) & "','" & a(98) & "','" & a(99) & "')"
Dim Command As SqlClient.SqlCommand = New SqlClient.SqlCommand(mSQL)
Dim x As Integer
Conn.Open()
Command.Connection = Conn
x = Command.ExecuteNonQuery()
Conn.Close()
Command = Nothing

Inschrijdatum is empty, so I expect that the field in the table will be TODAY and not 1/1/1900.

Ger.

|||

Two problems:

First, Inschrijdatum isn't empty, it's a zero length string.

Secondly, the only way you are going to get SQL Server to insert the default is one of three ways.

a) Don't mention the column in the insert at all.

b) Tell it to use the DEFAULT like INSERT ... VALUES (...,DEFAULT,...), note there is no quotes around DEFAULT, it is not a string.

c) Explicitly tell it the default value like INSERT ... VALUES (...,GetDate(),...)

That said, replace:

"','" &a(2) & "','"

with:

"'," & IIF(a(2)<>"","'" & a(2) & "'","DEFAULT") & ",'"

|||

Oh, I guess I should mention this code is susceptible to SQL injection attacks.

For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.

|||

Motley wrote:

Oh, I guess I should mention this code is susceptible to SQL injection attacks.

For example, type this in your field labeled "HtaBijzOpm": "','') TRUNCATE Medewerkers --" and submit your form, and you've just deleted all the records from your Medewerkers table.

Thanks Motley for replying I will reconstruct the inserts according your advice.

How can I avoid this SQL injection attack ?

Ger.

How To Set Column Default Value to include Text AND GetDate() ?

GETDATE() works alone as default in a DateTime column, but:

1) How do you include current Date concatenated with text like:

- Column default Value = 'Submitted 2007-07-30 13:15:54.953'

2) is it possible to truncate that DateTime value (deleting the seconds 54.953)

- Final Column default Value = 'Submitted 2007-07-30 13:15'

Thinking Cast or convert but unsuccessful to date:

- 'Submitted ' + Cast(CHAR(16),GETDATE()) ?

I think the short answer here is, 'Yes'; it is just a matter of getting the format that you want. Here is an example:

Code Snippet

declare @.what table (aVc varchar(30) default('Submitted ' + left(convert(varchar, getdate(),120), 16)) )
insert into @.what default values

select * from @.what

/*
aVc
Submitted 2007-07-30 13:15
*/

Giving it another look you might want to try making your default

default('Submitted ' + convert(varchar(16), getdate(),120))

Might simplify the expression a bit.

|||Thanks Kent!

How to separate the DateTime field into Two Fields in View

Hi ,

I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?

Best Regards,

make use of this

DATEPART

Returns an integer representing the specified datepart of the specified date.

Syntax

DATEPART ( datepart , date )

Arguments

datepart

Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft? SQL Server?.

Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.

Return Types

int

Remarks

The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.

Examples

The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.

SELECT GETDATE() AS 'Current Date' GO

Here is the result set:

Current Date Feb 18 1998 11:46PM SELECT DATEPART(month, GETDATE()) AS 'Month Number' GO

Here is the result set:

Month Number 2

This example assumes the date May 29.

SELECT DATEPART(month, GETDATE()) GO

Here is the result set:

-- 5 (1 row(s) affected)

In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

Here is the result set:

-- 1 1 1900 |||

With datepart you will have to write several statements to getthe correct parts and afterwards join them back concatenating fields.

depending exactly on what you need the correct path is:

a) use datediff as in (if you need dates)

select getdate() /*fulldatetime*/, cast(datediff(day,0,getdate()) as datetime) /*justdate*/, getdate()-cast(datediff(day,0,getdate()) as datetime) /*justtime*/

b) use convert as in (if your ok with strings)

select getdate() /*fulldatetime*/, convert( varchar(20),getdate(),102) /*justdate*/, convert( varchar(20),getdate(),108) /*justtime*/

a) is much faster then (b) and much much faster then datepart solution.

|||

Thanks very much,

how to separate date & time during export

I would like to export sql server 2005 tables to a text file using SSIS. It is easy to do this as a direct export. However, how could I separate the datetime field into two fields: one for date only and one for time only?

Sample:

select getdate() ==> 2007-08-30 14:42:11.870

V

select convert(char, getdate(), 111) as date, convert(char, getdate(), 108) as [time]

==> Date = 2007/08/30

==> Time = 14:44:50

Others datetime format

century century
(yy) (yyyy) standard output format
- - --
- 0 or 100 (*) default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mms
- 9 or 109 (*) default mon dd yyyy
milliseconds hh:mis:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default dd mon yyyy
milliseconds hh:mms:mmm(24h)
14 114 - hh:mis:mmm(24h)

(*) the default values (style 0 or 100, 9 or 109, and
13 or 113) always return the century (yyyy)

|||

You have to use Derived Column Transformation of Data Flow.

So:

1. Add a Data Flow task to Control Flow window

2.Add OleDB Source then Derived Column and Finaly Flat File Destination linked with arrow

3. Double Click Derived Column and

write Derived Column Name desired then <add as new column> to Derived Column then in expresion : (DT_DBTIME)YourDataColumnName and Data Type database time[DT_DBTIME]

in second line write Derived Column Name desired then Replace 'YourDataColumnName' and in expression (DT_DBDATE)YourDataColumnName and Data Type databasedate[DT_DBDATE]

|||

I would use this expressions in a derived column:

For Date:

RIGHT("00" + (DT_WSTR,2)MONTH(MyDate),2) + "-" + RIGHT("00" + (DT_WSTR,2)DAY(MyDate),2) + "-" + (DT_WSTR,4)YEAR(MyDate)

For time:

(DT_DBTIME)MyDate

How to send mails from sql server?

Hello,

Problem statement ::

1) I have a birthday database which stores name s and date of birth of the persons.

2) Whenever sysdate and date of birth are equal then i want the sqls erver to send a mail/alert to my mail id saying that to day is birthday of so and so person.

3) I ma working on YUKON.

4) Please let me know how to accomplish this task?

Thanks&Regards,

Sreekanth Ammisetty

ask_sreekanth@.hotmail.com

Hi Sreekanth.

You want to make use of Database Mail for this type of thing. See the following topic in SQL Server 2005 Books Online for more information, and repost any specific questions you may have after that:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/14cbf88f-d9d1-41a5-994e-532e2973ac9e.htm

|||

Hi Chad,

The link that you have provided is not working, however I ahve got solution for this to some extent. I am using "EXEC msdb.dbo.sp_send_dbmail ....." command to send the mails, But the problem I am getting is that SMTP service is not there in my desktop, I tried by installing the same, But still i could able to achieve the desired bahaviour, bcoz it is giving error related to SMTP.

The following is the error I am getting "A severe error occurred on the current command. The results, if any, should be discarded."

Please let me know the solution for this.

|||May try XPSMTP from http://www.sqldev.net/xp/xpsmtp.htm here.